How to delete empty rows starting from a certain column

[occitan]

Hello everybody!

I have a dataset in excel containing 25684 rows and 1868 columns.

The first 6 columns all contain name identifiers for all the rows (hence, there are no empty cells in the rows for the first 6 columns). However, from column 7 onwards, since there are numerical values, many rows have missing observations. So there are rows which always contain values from column 1-6, but after column 6 they have all empty values. I want to eliminate all these rows.

I was trying to set up a Macro through VBA which deleted rows having empty cells but WITHOUT considering the first 6 columns.

I tried this, but it doesn't work (It doesn't take consider the range I want for the elimination):


SubBlank_Rows()
Dim i As Long, LastRow as Long
SetmyRange = ActiveSheet.Range("G2:BSV25684")
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For i = Lastrow To 1 Step -1
If WorksheetFunction.CountA(Rows(i)) = 0 Then
Rows(i).EntireRow.Delete
End If
Next i
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub

Does someone know how to solve this?

Thank you very much!

O.

 

[Fluff]

Hi & welcome to the board.
Try

If WorksheetFunction.CountA(Range("G" & i).Resize(, 1862)) = 0 Then

 

[Scott T]

This deletes the entire row with out any regard to any selected range.

[COLOR=#000000][FONT=&quot]Rows(i).EntireRow.Delete[/FONT][/COLOR]

Does this do what you want?

Sub Blank_Rows()

Dim i As Long, LastRow As Long, lc As Long


With Application
    .Calculation = xlCalculationManual
    .ScreenUpdating = False


LastRow = Cells(Rows.Count, "A").End(xlUp).Row




For i = LastRow To 1 Step -1
    lc = Cells(i, Columns.Count).End(xlToLeft).Column
    If lc = 6 Then
        Range("G" & i & ":BSV" & i).Delete shift:=xlShiftUp
    End If
Next i
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub

 

[occitan]

Thank you Fluff and Scott!

Many thanks Scott! That was exaclty the code I was looking for!

You made my day! Thanks! ^_^

O