Iteratively delete rows from a spreadsheet

[Vicki Hagberg]

I have a very large spreadsheet (about 50,000 rows, and to CM of
columns) with blocks of data 20 rows high (rows 1-20 are from Building
A, rows 21-40 are from Building B, etc). However, not every row in
each block of 20 has information in it - some are just placeholders.
For example, some blocks may have rows 1-18 filled with data while
other blocks may have only 1-6 filled with data.

I am interested in programming a macro that would delete the
placeholder rows out of the spreadsheet based on a certain criteria.
This would probably halve the size of my spreadsheet.

In faux-code:

For row i
i = 1 to 50,000
If Column B = #NA
Delete row i


Is this something that can be done with a macro, or do I need to go
through all of these rows by hand? Obviously, I haven't programmed
macros before (I've taken code and run it), but I've programmed in
other languages (Java, C#). Could someone point me in the right
direction?

Thanks,

V. Hagberg

 

[JLGWhiz]

If you no longer want to maintain the 20 row grouping per building, then it
is better to work from the bottom up when deleteing rows. This will avoid
the inadvertant skipping of consecutive rows that meet the delete criteria
because of the default shift up.

Sub delRw()
Dim lr As Long
lr = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
For i = lr To 2
If Cells(i, 2) = #NA Then
Row(i).Delete
End If
Next
End Sub

 

[JLGWhiz]

Had a senior moment there.

Change the line:

For i = lr To 2

To:

For i = lr To 2 Step -1

 

[Dave Peterson]

Option Explicit
Sub Testme01()

Dim iRow as long
Dim LastRow as long
dim FirstRow as long
with worksheets("Somesheetnamehere")
FirstRow = 1
lastrow = .cells(.rows.count,"B").end(xlup).row
for irow = lastrow to firstrow step -1
if iserror(.cells(irow,"B").value) then
.rows(irow).delete
end if
next irow
end with

End Sub

This actually tested for any error in column B.

You may want to use:
if lcase(.cells(irow,"B").text) = lcase("#N/A") then

for just #n/a's.