Change code from delete entire row to delete a range of cells in a row

[amitcohen]

Hi Guys
I'm using the following code to delete empty rows

Sub DeleteEmptyRows()
'only if entire row is blank
LastRow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
For R = LastRow To 1 Step -1
If Application.CountA(Rows(R)) = 0 Then
Rows(R).Delete
End If
Next R
End Sub

But its only works on entire row.

How to change the code so it will delete only first 10 cells that are empty.

In other words, instead of deleting all row no 4 (for instance)
It will only delete "A4:J4".
and on.. and on..

Many thanks for your help.
Amit

 

[Norie]

Amit

Is there anything else you want to change?

If the criteria for deletion is that the entire row is empty how will deleting 10 cells make any difference.

 

[VoG]

Try

Sub DeleteEmptyRows()
Dim LastRow As Long, R As Long
'only if entire row is blank
LastRow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
For R = LastRow To 1 Step -1
    If Application.CountA(Range("A" & R).Resize(, 10)) = 0 Then
        Range("A" & R).Resize(, 10).Delete shift:=xlShiftUp
    End If
Next R
End Sub

 

[amitcohen]

Hi Norie

I see your point here

Its just a code I used in other file, now in a need to use same concept
in a new file where columns 10 (J) and higher are in use..
and cannot be deleted.

Sorry for not making myself clear on that issue.

 

[amitcohen]

Hi VoG
It works like charm!!

Thanks,
Amit