CODEWITHSUNDEEP
excelvba
Chu
Chu

Reputation: 17

Delete row if cell is blank

I have two sheets in my worksheet;Sheet1 and Sheet2. Sheet1 allows user to enter file location and then ask the latter to select a range and then import data onto sheet2. However, there are 2 blank cells (that could obviously be removed manually) but I'm trying to automate the process but the code below tells me no cells have been found.

Sum DeleteRows()
Dim SrchNom
Dim Tb as Range

'we get sheet on which data has been copied

Set Tb = Sheet1.Range("E9")

Set SrchNom = Worksheets(Tb.Value).Range("J1", Worksheets(Tb.Value).Range("J1048576").End(xlUp))

SrchNom.SpecialCells(xlCellTypeBlanks).EntireRow.Delete

End sub

I want to be able to delete entire row if one cell in column J of sheet Tb is blank. How do I modify this code to be able to do what I am trying to do?

Upvotes: 0

Views: 908

Answers (2)

JvdV
JvdV

Reputation: 75960

Try:

Sub DeleteRows()
    Worksheets(Sheets(1).Cells(9, 5).Value).Columns("J").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

If you may encounter blank cells aren't actually blank due to newline, spaces or a combination you might find the answer given to this question interesting.

Might this be the case, also know you can iterate backwards through a range deleting all rows in which column J might meet certain conditions, possibly Chr(10) or Chr(32).

Upvotes: 0

Peter Ksenak
Peter Ksenak

Reputation: 315

you can use this code, just replace (.Rows(row)) with your range

Public Sub deleteRows()

Dim wks As Excel.Worksheet
Dim rng As Excel.Range
Dim row As Long
Dim lastRow As Long

Set wks = Excel.ActiveSheet
lastRow = Range("A" & Rows.count).End(xlUp).row

With wks
    For row = 1 To lastRow
        If Application.WorksheetFunction.CountA(.Rows(row)) = 0 Then
            If rng Is Nothing Then
                Set rng = .Rows(row)
            Else
                Set rng = Excel.Union(rng, .Rows(row))
            End If
        End If
    Next row
End With

'In order to avoid Run-time error check if [rng] range is not empty, before removing it.
If Not rng Is Nothing Then
    Call rng.EntireRow.Delete
End If
End Sub

Upvotes: 0

Related Questions