VBA Delete row based on multiple columns of data

[jlclyde]

I have multiple columns of data that I am currently moving from one
workbook to another. After it is moved, I want to delete all of the
rows that are blank in columns J,M,S, and V. I can not think of the
code to do this.

Thanks,
Jay

 

[Ron de Bruin]

Hi

Try this for the activesheet

Sub Loop_Example()
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

'We use the ActiveSheet but you can replace this with
'Sheets("MySheet")if you want
With ActiveSheet

'We select the sheet so we can change the window view
.Select

'If you are in Page Break Preview Or Page Layout view go
'back to normal view, we do this for speed
ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView

'Turn off Page Breaks, we do this for speed
.DisplayPageBreaks = False

'Set the first and last row to loop through
Firstrow = .UsedRange.Cells(1).Row
Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row

'We loop from Lastrow to Firstrow (bottom to top)
For Lrow = Lastrow To Firstrow Step -1

If Application.CountA(.Cells(Lrow, 1).Range("J1,M1,S1,V1")) = 0 Then .Rows(Lrow).Delete
'This will delete the row if the first 5 cells in the row are empty

Next Lrow

End With

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

End Sub

See also my code page
http://www.rondebruin.nl/delete.htm

 

[Ron de Bruin]

Remove the text

'This will delete the row if the first 5 cells in the row are empty

This is from another example

 

[Don Guillett]

something like this assumes col A is the longest col

Sub delrowifblanks() 'assumes min 2 characters in cell
For i = Cells(Rows.Count, "a").End(xlUp).Row To 17 Step -1
If Len(Cells(i, "j")) < 2 And _
Len(Cells(i, "m")) < 2 And _
Len(Cells(i, "s")) < 2 And _
Len(Cells(i, "v")) < 2 Then
Rows(i).Delete
End If
Next i
End Sub