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
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