How do I select a range using a macro?

[Guest]

Via macro, I would like to delete all rows that have a value of one (1) in
column H. All rows that have a value of (1) will be contiguous, meaning they
will follow one after the other (they won't be separate by rows in between
that do not have a value of (1) in column H); however, where the rows with a
value of (1) begin and end will vary each time the macro is used.

For example, rows 1 through 10 will not have a value of (1) in column H, but
rows 11 through 14 will. The next time the macro is run, it may be that rows
one through eight won't have a value of (1) in column H, but rows nine
through eleven will.

I'd like the macro to identify these rows with a value of (1) in column H,
select just those rows, and delete them.

Thanks for the help!

 

[Bob Phillips]

Public Sub test()
Dim iLastRow As Long
Dim i As Long
Dim iStart As Long
Dim iEnd As Long

With ActiveSheet

iLastRow = .Cells(.Rows.Count, "H").End(xlUp).Row
For i = 1 To iLastRow + 1
If Cells(i, "H").Value = 1 Then
iStart = i
Do
i = i + 1
Loop Until Cells(i, "H").Value <> 1
iEnd = i - 1
Exit For
End If
Next i

If iStart > 0 Then Rows(iStart & ":" & iEnd).Delete

End With

End Sub


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Gord Dibben]

Sub delete_some_rows()
Dim C As Range
With Columns("H")
Do
Set C = .Find(1, LookIn:=xlValues, LookAt:=xlWhole, _
MatchCase:=False)
If C Is Nothing Then Exit Do
C.EntireRow.Delete
Loop
End With
End Sub


Gord Dibben MS Excel MVP

 

[Guest]

Bob and Gord, thank you for your replies! I ended up going with Gord's
simply because the coding was shorter. It was exactly what I was looking for.

Brett