VBA delete empty rows but with ""

[JokevdVen]

Hello VBA experts,

I need to delete empty rows in a long list with data however the cells are not completely blank but contain "".

I have tried the following code but it isnot working. Anybody who can advise me how to do it??


Sub deleteR()

Sheet10.Activate
Dim r, n As Range
Set n = Range("A2", Range("A6000").End(xlUp))

For Each r In n
If r.Value = "" Then
r.EntireRow.delete
End If
Next r
End Sub


Thanks !

Joke

 

[MattOCUSA]

Use """" instead of ""

 

[jindon]

1) Loop backwards when you delete/insert Row(s)
2) If you want to use For Each loop then try

Sub deleteR()
Dim r, txt As String
Again:
With Sheet10
     For Each r In .Range("a2",.Range("a" & Rows.Count).End(xlUp))
          If r.Value = "" Then
                txt = txt & "," & r.Address(0,0)
                If Len(txt) > 245 Then
                     .Range(Mid$(txt,2)).EntireRow.Delete
                     txt = "" : GoTo Again
                End If
          End If
     Next r
     If Len(txt) Then .Range(Mid$(txt,2)).EntireRow.Delete
End With
End Sub

 

[Krishnakumar]

Hi,

Sub DeleteRows()
Dim a, i As Long, w(), j As Long
a = Range("a2", Range("a" & Rows.Count).End(xlUp))
ReDim w(1 To UBound(a, 1), 1 To 1)
For i = 1 To UBound(a, 1)
    If Len(a(i, 1)) > 0 Then
        j = j + 1: w(j, 1) = a(i, 1)
    End If
Next
With Range("a2")
    .CurrentRegion.Resize(, 1).Offset(1).ClearContents
    .Resize(j).Value = w
End With
End Sub

HTH