Macro to remove rows

[Kueck]

I need a macro that will delete all rows that don't have an "account number"
in a certain column. The spreadsheet has been imported from a text format
and I need to delete the rows that include the page headers, column headings
etc.

Thanks.

 

[Chip Pearson]

Try something like the following:

Sub DeleteThem()
Const TEST_COL = "B" '<<< Change to appropriate column
Const DATA_COL = "C" '<<< Change to appropriate column
Const TOP_ROW = 2 '<<< Change to first row with data
Const SHEET_NAME = "Sheet2" '<<< Change to sheet name
Dim RowNdx As Long
Dim LastRow As Long
Dim WS As Worksheet

Set WS = ThisWorkbook.Worksheets(SHEET_NAME)
With WS
LastRow = .Cells(.Rows.Count, TEST_COL).End(xlUp).Row
If LastRow < TOP_ROW Then
Exit Sub
End If
For RowNdx = LastRow To TOP_ROW Step -1
If .Cells(RowNdx, DATA_COL).Value = vbNullString Then
.Rows(RowNdx).Delete
End If
Next RowNdx
End With
End Sub


Change TEST_COL to the column letter that is used to find the last row
of data. The deletion process will start at the last non-blank row in
this column. Change DATA_COL to the column letter that contains your
account numbers. This can be, but need not be, the same as TEST_COL.
Change TOP_ROW to the row number of the first row in the worksheet
that contains data subject to deletion. If your worksheet contains
heading rows, set TOP_ROW to the row below your heading section. No
rows above TOP_ROW will be deleted, regardless of their content.
Change SHEET_NAME to the name of the worksheet containing the data.

Then, just run the code.

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)