We help IT Professionals succeed at work.

Excel Macro to delete rows where the string value of cells in column B starts with certain combinations of letters

FaheemAhmadGul
FaheemAhmadGul used Ask the Experts™
on
In an Excel Worksheet I have several thousand records. The value of Column B always contains a string which can be from 2 letters to perhaps up to 10 letters long.

I need help with VBA macro which will delete all rows (records) in this Excel Worksheet where the value of the string present in Column B start with the letters ”tka”. When a row is deleted all the rows below it should move upwards. The number of records in this Worksheet keeps changing so at the start the Macro will need to calculate how many records we have in the Worksheet and then loop accordingly.

So if there are four records in the Worksheet with the value in Column B as below
1. Appletton
2. Tkaapple
3. Btaorange
4. Tkapink

After the macro runs we will be left with three records in our sheet.
1. Appletton
2. Btaorange
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hardware Tester and Debugger
Commented:
Hi there! :)

I suggest doing the counting of data sets and deleting of records in two separate Macros. I don't recommend combining both since you probably won't need to run both steps every time together.

To delete the entire row if column B contains tka:
Sub DeleteRowsIfCellsInColumnBContainsTka()
  With Columns("B")
    .Replace "*tka*", "", xlWhole
    .SpecialCells(xlCellTypeBlanks).EntireRow.Delete (xlShiftUp)
  End With
End Sub

Open in new window


Counting the Total Number of Rows With Data:
Sub AddRowToFinalCountIfDataExistsinColumnB()
    Dim n As Long
    
    With ActiveSheet
    n = WorksheetFunction.CountA(Range("B:B"))
    MsgBox ("There is a total of " & n & " existing row(s) with data based on Column B."), vbDefaultButton1 + vbInformation, "Count Number of Rows Based on Column B"
    End With
End Sub

Open in new window

Author

Commented:
Worked perfectly!!!
Many thanks.
NoahHardware Tester and Debugger

Commented:
@FaheemAhmadGul You're welcome! Glad I was of help :)