VBA, changing Columns for Rows

[Farback]

I use this code to look down coumn C and to delete any row that had "Value A" or "Value" in it:

Sub Delete_Surplus_Rows()
Dim FindString As String
Dim iRow As Long
Dim LastRow As Long
Dim FirstRow As Long
Dim CalcMode As Long
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
FirstRow = 4
With ActiveSheet
.DisplayPageBreaks = False
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = LastRow To FirstRow Step -1

If IsError(.Cells(iRow, "C").Value) Then
'Do nothing
'This avoids an error if there is a error in the cell
ElseIf .Cells(iRow, "C").Value = "Value A" Then
.Rows(iRow).Delete
ElseIf .Cells(iRow, "C").Value = "Value B" Then
.Rows(iRow).Delete
End If

Next iRow

End With
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub

In another spreadsheet, I now need to look along row 3 for two values, say "Value C" and "Value B", and to delete the columns that contain them. How do I amend my code to do this?

 

[Weaver]

I thought you might.

Try:

Set delRange = range("C5",cells(rows.count,"C").end(xlup))

Alternatively, right after

delRows.EntireRow.Hidden = True

insert the line

range("A1:A4").EntireRow.Hidden = True

so they become hidden (and thus avoid being deleted)
There's probably a couple more ways, like not using 'cells' for the delete range and defining a range that doesn't include rows 1 - 4, or making delRange start off by including the other rows you want to keep

 

[Farback]

Hi Weaver. Sorry for not responding sooner, got involved in other things. I used your second solution (hiding the top 4 rows) and it works fine. Once again many thanks for your assistance, it's much appreciated.