How to delete the rows based in excel sheet using column values

Question

I have excel with 5 different sheets. sheet3 and sheet4 i want delete rows based on the single column cell value. in sheet 3 i want to delete rows based on H column cell values if H2="#N/A" and H503="#N/A" then delete entire rows. in sheet 4 i want to delete rows based on b column cell values if B2="320857876",B3="32085678",B4="12133435" the delete the entire rows where B column cell values starts with 302. and i want to delete all Data from 'C' column My excel sheet is like this

Using excel file

Sub Create()
  Dim LastRow As Long
  Dim i As Long

  LastRow = Range("B10000").End(xlUp).Row
  For i = LastRow To 1 Step -1
    If Range("B" & i) = "#N/A" Then
        Range("B" & i).EntireRow.Delete
    End If
  Next
End Sub

Skin · Accepted Answer · 2019-04-19 11:48:50Z

0

You've got a few requirements there and your code is fairly light but regarding the #N/A part of it, you can't just test for that text using the value approach, which is the default property returned for a range object.

Sub Create()
    Dim LastRow As Long, i As Long

    LastRow = Range("B10000").End(xlUp).Row

    For i = LastRow To 1 Step -1
        If Range("B" & i).Text = "#N/A" Then
            Range("B" & i).EntireRow.Delete
        End If
    Next
End Sub

... you need to use .Text to get that to work, or, If IsError(Range("B" & i)) Then is another approach.

The rest of your requirements is just logic. The rest of your code is relatively sound so you just need to work through it.

I hope that helps.

answered Apr 19, 2019 at 11:48

Skin

11.7k3 gold badges16 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

bm13563 · Accepted Answer · 2019-04-20 21:38:26Z

Sub delete_rows()

    Dim sheet As Worksheet, cell As Range

    Count = 1
    For Each sheet In ThisWorkbook.Worksheets
        If Count = 3 Then
            lastrow = sheet.Cells(sheet.Rows.Count, "H").End(xlUp).Row
            Set Rng = sheet.Range("H1:H" & lastrow)

            For i = Rng.Cells.Count To 1 Step -1
                If Application.WorksheetFunction.IsNA(Rng(i).Value) Then
                    Rng(i).EntireRow.Delete
                ElseIf Rng(i).Value = "#NA" Then
                    Rng(i).EntireRow.Delete
                End If
            Next

        ElseIf Count = 4 Then
            lastrow = sheet.Cells(sheet.Rows.Count, "B").End(xlUp).Row
            Set Rng = sheet.Range("B1:B" & lastrow)
            Debug.Print (Rng(4).Text)

            If Rng(2).Value = "320857876" And Rng(3).Value = "32085678" And Rng(4).Value = "12133435" Then
                For i = Rng.Cells.Count To 1 Step -1
                    If Left(Rng(i).Value, 3) = "302" Then
                        Rng(i).EntireRow.Delete
                    End If
                Next
            End If

            lastrow = sheet.Cells(sheet.Rows.Count, "C").End(xlUp).Row
            Set Rng = sheet.Range("C1:C" & lastrow)

            For Each cell In Rng
                cell.Value = ""
            Next cell

        End If

        Count = Count + 1

    Next

End Sub

Collectives™ on Stack Overflow

How to delete the rows based in excel sheet using column values

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related