VBA code for Deleting rows by verification

H

hoffman3

Alright i have a list of variables like this
desired output after vba:
a 1 6 a 1 6
b 2 2 c 3 1
c 3 1
d 4 8

Is there a way to alter the row deletion code to search multipl
columns for the number one and if the row doesn't have a 1 then i
will be deleted from the sheet. Thanks
Eric Hoffma
 
H

hoffman3

chart suppose to look like this:

a 1 6
b 2 2
c 3 1
d 4 8


desired output after vba:
a 1 6
c 3
 
R

Ron de Bruin

Try this

This will check each whole row for the value 1

Sub Example1()
Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long

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

Firstrow = ActiveSheet.UsedRange.Cells(1).Row
Lastrow = ActiveSheet.UsedRange.Rows.Count + Firstrow - 1
With ActiveSheet
.DisplayPageBreaks = False
For Lrow = Lastrow To Firstrow Step -1

If Application.WorksheetFunction.CountIf(.Rows(Lrow), "1") = 0 _
Then .Rows(Lrow).Delete
Next
End With
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub
 
H

hoffman3

The code in the last reply just seemed to freeze up excel, i waited fo
5 minutes then finally just restarted it. Thanks for the time though.

Eric Hoffma
 
Top