Search Contextures Sites
Rick Rothstein - Excel VBA Sample Code - Text
The Excel CONCATENATE function would be much improved if you could specify a range of cells to concatenate, and the delimiter that you'd like between the joined cell values.
This ConCat UDF (user defined function), from Excel MVP Rick Rothstein, concatenates a range of cells, and provides the option to insert a common delimiter between the elements (think comma separated listing as one possible use).
You can omit the delimiter, but leave the comma in as a placeholder.
Function ConCat(Delimiter As Variant, ParamArray CellRanges() As Variant) As String Dim Cell As Range, Area As Variant If IsMissing(Delimiter) Then Delimiter = "" For Each Area In CellRanges If TypeName(Area) = "Range" Then For Each Cell In Area If Len(Cell.Value) Then ConCat = ConCat & Delimiter & Cell.Value Next Else ConCat = ConCat & Delimiter & Area End If Next ConCat = Mid(ConCat, Len(Delimiter) + 1) End Function
Install the ConCat UDF
If you have never installed a UDF, the procedure is simple... press Alt+F11 to go into the VB editor, click Insert/Module from its menu bar and then Copy/Paste the above code into the code window that opened up. That's it.
Use the ConCat UDF
Go back to your worksheet put some text in, say A1, A2, A3, C1, D1, D2 and then type this formula into an unused cell...
and the contents of those cells (and the word "HELLO") will be concantenated together, in the order shown, with a dash between them.
The delimiter (first argument) is required, but if you want to concatenate cells, cell ranges or text together without a delimiter, you can specify the empty string ("") as the first argument or simply omit it (but still use the comma as a placeholder in order to delineate the argument position).
So, you could concatenate my above example cells and text, but with no delimiter between them, either like this...
or like this (note the leading comma)...
Using the ConCat UDF in Other Workbooks
If you install the ConCat UDF into a workbooks, then the function will travel with the file if you distribute it to others.
If you find this ConCat UDF useful and want it available for use on any worksheets that only YOU will work on, just install it in your personal.xls file... just remember, though, if you install it to your personal.xls file and use if from there, then the function will NOT travel with any worksheets you distribute to others (meaning cells using ConCat will produce a #NAME! error on their computers) unless, of course, they install the function to their own personal.xls file as well.
If you want to pursue the personal.xls file route, and you don't now have one, you can find out how to create one here...
Here's an Excel macro that will delete all the rows that contain the specified word in the column that you set it to search. The code will search for instances where the whole cell is equal to the word(s) that you enter, not partial matches. Because the code uses the SpecialCells method, it is extremely fast -- much faster than looping through a range of cells.
Sub DeleteRowsWithWord() Dim Col As Variant, Word As String Col = InputBox("Which column has the word(s) you want to search for?") If Len(Col) > 0 And Not Col Like "*[!0-9]*" Then Col = Val(Col) Word = InputBox("What is the word whose rows you want to delete?") With Columns(Col) .Replace Word, "#N/A", xlWhole .SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete End With End Sub
This code can be used whether there are empty cells in the range being searched or not. The only important part of the code is the With/End With block (the code before it is provides but one of many methods of specifying the column to be searched and the word to be searched for).
Run the Macro
When you run the macro, you'll be prompted to enter a column:
and a word whose rows you want to delete:
In a reverse of the previous code sample, Rick created this code to delete all the rows where the specified word DOES NOT appear. The code will search for instances where the whole cell is equal to the word(s) that you enter, not partial matches. Because the code uses the SpecialCells method, it is extremely fast -- much faster than looping through a range of cells.
Sub DeleteRowsWithoutWord() Dim Col As Variant, Word As String, LastRow As Long Col = InputBox("Which column has the word(s) you want to search for?") If Len(Col) > 0 And Not Col Like "*[!0-9]*" Then Col = Val(Col) Word = InputBox("What is the word whose rows you do NOT want to delete?") LastRow = Cells(Rows.Count, Col).End(xlUp).Row On Error Resume Next With Range(Cells(1, Col), Cells(LastRow, Col)) .Replace Word, "#N/A", xlWhole .SpecialCells(xlCellTypeBlanks).EntireRow.Delete .SpecialCells(xlCellTypeConstants, xlLogical + xlNumbers + xlTextValues).EntireRow.Delete .SpecialCells(xlCellTypeConstants, xlErrors).Value = Word End With End Sub
Run the Macro
When you run the macro, you'll be prompted to enter a column (as shown in the previous macro), and the word whose rows you DO NOT want to delete.
I graduated college as a Math Major and then ended up working for some 32+ years as a Civil Engineer (Road Design) for the New Jersey Department of Transportation. I originally started programming in BASIC on a Texas Instruments TI-99/4 as a hobby.
Eventually I formed a company (FFF Software) with two other gentleman and we sold a couple of TI-BASIC programs commercially for a couple of years. I also sold several articles on programming the TI-99/4 to COMPUTE! Magazine. Once TI went out of the home computer market, I purchased a Radio Shack Model 100 (first truly portable computer) and programmed on it (again, as a hobby) for a couple of years. Again, I sold several articles to a couple of magazines that covered this particular computer.
Before buying my first IBM compatible PC, I owned a Commodore 128 and Atari 520. Once I discovered Visual Basic, I was completely hooked. I continued writing articles on and off across the years, but now exclusively for Visual Basic; and, of course, I used Visual Basic in my workplace to make my job, and the job of my co-workers, easier.
About a year or so before I retired, I decided to start helping others with their programming needs by volunteering to answer questions on various Visual Basic newsgroups. In 2007 or so, I branched out and began answering questions on Excel newsgroups as well. I have been retired for a few years now but my Excel (and now to a lesser extent VB) newsgroup volunteer efforts are still continuing to this very day.
Related Excel VBA Tutorials
Contextures Inc., Copyright ©2013
All rights reserved.
Last updated: July 12, 2013