Delete Row If Blank or Zero

C

Connie

I am using the following code to delete blank or zero rows on a sheet.
If I wanted to use only columns C through the end column to determine
whether the row is blank or zero, how would I do that? In other words,
I would like to modify the following to perform the counting or summing
beginning with row C rather than all the columns in the row:

If Application.CountA(sh.Rows(i)) = 0 Then
sh.Rows(i).Delete
ElseIf Application.Sum(sh.Rows(i)) = 0 Then
sh.Rows(i).Delete
End If

Any help would be appreciated. Thanks! Connie
***************************************************************************************************

Private Sub CreateUploadFile_Click()
Dim sh As Worksheet
Set sh = Worksheets("Upload Data")
Set rng = GetRealLastCell(sh)
'delete blank rows in upload file
For i = rng.Row To 2 Step -1
If Application.CountA(sh.Rows(i)) = 0 Then
sh.Rows(i).Delete
ElseIf Application.Sum(sh.Rows(i)) = 0 Then
sh.Rows(i).Delete
End If
Next
End Sub

Function GetRealLastCell(sh As Worksheet) As Range
Dim RealLastRow As Long
Dim RealLastColumn As Long
On Error Resume Next
RealLastRow = _
sh.Cells.Find("*", sh.Range("A1"), , , xlByRows, xlPrevious).Row
RealLastColumn = _
sh.Cells.Find("*", sh.Range("A1"), , , xlByColumns,
xlPrevious).Column
Set GetRealLastCell = sh.Cells(RealLastRow, RealLastColumn)
End Function
 
B

Bob Phillips

Using the amended GetLastRealCell I gave in the last post

Private Sub CreateUploadFile_Click()
Dim sh As Worksheet
Dim iLastRow As Long
Dim iLastCol As Long
Set sh = Worksheets("Upload Data")
Set rng = GetRealLastCell(sh, iLastRow, iLastCol)
'delete blank rows in upload file
For i = rng.Row To 2 Step -1
If Application.CountA(sh.Range(sh.Cells(i, "C"), sh.Cells(i,
iLastCol))) = 0 Then
sh.Rows(i).Delete
ElseIf Application.Sum(sh.Range(sh.Cells(i, "C"), sh.Cells(i,
"IV"))) = 0 Then
sh.Rows(i).Delete
End If
Next
End Sub



--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top