Trying to delete rows with no value

[tuxalot]

Trying to delete rows with no values (but contain formulas, conditional formatting) from the bottom up in a three page worksheet prior to printing. During execution, after row is found with data, exit sub.

ok, here's what I got so far...
The code below doesn't work probably because each blank row in my worksheet contains formulas. The CountA function did not see these rows as blank (I think), so therefore didn't delete them. Can I code so that the CountA function evaluates rows for VALUES only? If no values, then delete. See code below.

Thanks for your help.

Dim R As Long
Dim C As Range
Dim N As Long
Dim Rng As Range

'On Error GoTo EndMacro

If Selection.Rows.Count > 1 Then
Set Rng = Selection
Else
Set Rng = ActiveSheet.UsedRange.Rows
End If
N = 0
For R = Rng.Rows.Count To 1 Step -1
If Application.WorksheetFunction.CountA(Rng.Rows(R).EntireRow) = 0 Then
Rng.Rows(R).EntireRow.Delete
'Rows(Rng.Row).Hidden = True 'I woulk like to just hide them
N = N + 1
Else
Exit For
End If
Next R

 

[StevePB]

Hi skywaz,

Try looking in the Excel VBA help for "SpecialCells". This gave me a list of constants for the SpecialCells method (this is the equivalent of going to "Edit >> Go To >> Cell >> Special&quot, including xlCellTypeAllFormatConditions and xlCellTypeFormulas - this might help ?

Regards, SteveB.

 

[dwilson01]

This may work for you, what it does is combine all the cells of a given row and check the length of the string. If the string length is zero, the row color is changed to red. There are any number of things you could do at that point, you could delete the row, set a print area, hide the rows...

This works for formulae that are evaluated to an empty string, not sure about conditional formating. Give it a try and let us know.

Dim oRow As Object, oCell As Object, strUnion As String

For Each oRow In ActiveSheet.UsedRange.Rows
  For Each oCell In oRow.Cells
    strUnion = strUnion & oCell
  Next oCell
  If Not Len(strUnion) > 0 Then oRow.Interior.ColorIndex = 3
  strUnion = ""
Next oRow

Dave

 

[tuxalot]

Steve and Dave,

Thanks for your input. I appreciate it. I will try your suggestions later today. I'll let you know how I fare.

Thanks again,

Darin