CODEWITHSUNDEEP
excelvba
markerbean
markerbean

Reputation: 145

Delete all rows with specific text and range

This is my code so far. Problem is that it deletes the first row. I want to exclude first row (the header). Because the rows i was deleting was duplicate headers

[Code] Dim Firstrow As Long Dim Lastrow As Long Dim Lrow As Long Dim CalcMode As Long Dim ViewMode As Long

With Application
    CalcMode = .Calculation
    .Calculation = xlCalculationManual
    .ScreenUpdating = False
End With


With ActiveSheet.Select


    ViewMode = ActiveWindow.View
    ActiveWindow.View = xlNormalView


    .DisplayPageBreaks = False


    Firstrow = .UsedRange.Cells(2).Row
    Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row


    For Lrow = Lastrow To Firstrow Step -2


        With .Cells(Lrow, "D")

            If Not IsError(.Value) Then

                If .Value = "Service Tower" Then .EntireRow.Delete

            End If

        End With

    Next Lrow

End With

ActiveWindow.View = ViewMode
With Application
    .ScreenUpdating = True
    .Calculation = CalcMode
End With [code]

Upvotes: 0

Views: 102

Answers (1)

Tim Williams
Tim Williams

Reputation: 166306

.UsedRange.Cells(2)

is the second cell on the first row of the UsedRange. Cells are counted left-to-right then top-to-bottom (ie. "row-major" not "column-major")

You want

Firstrow = .UsedRange.Rows(2).Row

Upvotes: 1

Related Questions