Rick Rothstein Excel VBA Sample Code Text

Search Contextures Sites

Rick Rothstein - Excel VBA Sample Code - Text
Combine Cell Values and Delimiter
Delete Row With Specific Word
Delete Rows Without Specific Word

About Rick Rothstein
Related Excel VBA Tutorials
Combine Cell Values and Delimiter

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...

=ConCat("-",A1:A3,C1,"HELLO",D1:D2)

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...

=ConCat("",A1:A3,C1,"HELLO",D1:D2)

or like this (note the leading comma)...

=ConCat(,A1:A3,C1,"HELLO",D1:D2)

your choice.

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...

http://office.microsoft.com/en-us/excel-help/deploy-your-excel-macros-from-a-central-file-HA001087296.aspx
Delete Row With Specific Word

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:
Delete Rows Without Specific Word

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.
About Rick Rothstein

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.

Rick Rothstein

Rick Rothstein

Related Excel VBA Tutorials

Excel VBA -- Adding Code to a Workbook

Create an Excel UserForm

Excel UserForm With ComboBoxes

Excel VBA ComboBox Lists

Home Excel Tips Excel Files The Excel Store Blog Contact

Privacy Policy

Contextures Inc., Copyright �2010
All rights reserved.

Debra Dalgleish

Last updated: August 10, 2010

	Combine Cell Values and Delimiter Delete Row With Specific Word Delete Rows Without Specific Word About Rick Rothstein Related Excel VBA Tutorials

	Combine Cell Values and Delimiter 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... =ConCat("-",A1:A3,C1,"HELLO",D1:D2) 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... =ConCat("",A1:A3,C1,"HELLO",D1:D2) or like this (note the leading comma)... =ConCat(,A1:A3,C1,"HELLO",D1:D2) your choice. 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... http://office.microsoft.com/en-us/excel-help/deploy-your-excel-macros-from-a-central-file-HA001087296.aspx

	Delete Row With Specific Word 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:




	Delete Rows Without Specific Word 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.

Excel VBA -- Adding Code to a Workbook Create an Excel UserForm Excel UserForm With ComboBoxes Excel VBA ComboBox Lists