CODEWITHSUNDEEP
vbaexcel
MTBthePRO
MTBthePRO

Reputation: 520

How to read empty and filled rows then delete filled rows if value is 0?

I am having difficulty writing a code that reads empty rows (gaps) and reads filled (Title) rows from Column C but only deletes empty rows with titles, not empty rows (gaps).

If a code can be created to read of Column C for row titles and empty gaps; or just gaps which do not have any titles in Column C. Then if a row with the title is empty from Column D until L and n/m means nothing which means it's empty, that row should be deleted but not removing the gaps. This picture shows the formatting of the file and the fourth line needs to be removed. Any help would be appreciated

enter image description here

My code with some suede code. I am a little confused how I would go about this..

   Dim WS As Worksheet
     For Each WS In Sheets
     WS.Activate

       Dim n As Long
         Dim nlast As Long
         Dim rw As Range
         Set rw = ActiveWorkbook.ActiveSheet.UsedRange
         nlast = rw.count

         For n = nlast To 9 Step -1
          If (rw.Cells(n, 3).Value = "") Then
          keep
          ElseIf (rw.Cells(n, 3).Value = text) Then
          keep but
          Else (rw.Cells(n, 4).Value = "" And rw.Cells(n, 5).Value = "" And rw.Cells(n, 6).Value = "" And rw.Cells(n, 7).Value = "" And rw.Cells(n, 8).Value = "" And rw.Cells(n, 9).Value = "" And rw.Cells(n, 10).Value = "" And rw.Cells(n, 11).Value = "") Then
          rw.Rows(n).Delete

             End If
         Next n
         Next WS
     End Sub

Upvotes: 1

Views: 71

Answers (1)

VBA Pete
VBA Pete

Reputation: 2666

You are pretty close. Here is the fixed code:

 Sub Test()

Dim WS As Worksheet
Dim n As Long
Dim nlast As Long


For Each WS In Sheets

nlast = WS.UsedRange.Rows(WS.UsedRange.Rows.Count).Row

    For n = nlast To 9 Step -1
        If WS.Cells(n, 3).Value <> "" And WS.Cells(n, 4).Value = "" And WS.Cells(n, 5).Value = "" And _
        WS.Cells(n, 6).Value = "" And WS.Cells(n, 7).Value = "" And WS.Cells(n, 8).Value = "" And _
        WS.Cells(n, 9).Value = "" And WS.Cells(n, 10).Value = "" And WS.Cells(n, 11).Value = "" Then

        WS.Rows(n).EntireRow.Delete

        End If
    Next n
Next WS
End Sub

Upvotes: 1

Related Questions