Remove Rows with Blanks for named worksheet

oplintx

New Member
Joined
Mar 1, 2014
Messages
28
I am needing some vba code to delete rows if columns contain blanks (specifically column E) and define the sheet name as "data". I have 20000 records in this table so I need something efficient. Any help would be appreciated.

Supplier
Terminal
Product
Location
Manager
Customer
Price
Rank
Eff.Date
Eff.Time
Supplier2
Terminal18
Product5
Location1
Manager1
Customer1
Price
1
6/6/18
12:00 AM
Supplier2
Terminal18
Product5
Location1
Manager1
Customer1
Price
1
6/6/18
12:00 AM
Supplier3
Terminal8
Product5
Location6
Manager2
Customer2
Price
3
6/5/18
6:00 PM
Supplier3
Terminal8
Product5
Location6
Manager2
Customer2
Price
3
6/5/18
6:00 PM
Supplier3
Terminal8
Product5
Location6
Manager2
Customer2
Price
8
6/5/18
6:00 PM
Supplier3
Terminal8
Product5
Location6
Manager2
Customer2
Price
8
6/5/18
6:00 PM
Supplier4
Terminal10
Product5
Location6
Manager2
Customer2
Price
5
6/5/18
6:00 PM
Supplier4
Terminal10
Product5
Location6
Manager2
Customer2
Price
5
6/5/18
6:00 PM
Supplier5
Terminal5
Product5
Location5
Customer2
Supplier5
Terminal5
Product5
Location5
Customer2
Supplier5
Terminal6
Product1
Location5
Customer2
Supplier5
Terminal6
Product5
Location5
Manager2
Customer2
Price
2
6/6/18
12:01 AM
Supplier5
Terminal6
Product5
Location5
Manager2
Customer2
Price
2
6/6/18
12:01 AM
Supplier5
Terminal7
Product5
Location11
Manager2
Customer2
Price
3
6/5/18
6:00 PM
Supplier5
Terminal7
Product5
Location11
Manager2
Customer2
Price
2
6/5/18
6:00 PM
Supplier5
Terminal12
Product1
Location10
Customer2
Supplier5
Terminal12
Product5
Location10
Customer2
Supplier5
Terminal12
Product5
Location10
Customer2
Supplier5
Terminal14
Product5
Location11
Manager3
Customer2
Price
1
6/6/18
12:01 AM
Supplier5
Terminal14
Product5
Location11
Manager3
Customer2
Price
1
6/6/18
12:01 AM
Supplier6
Terminal7
Product1
Location11
Customer2
Supplier6
Terminal7
Product5
Location11
Manager3
Customer2
Price
3
6/6/18
12:01 AM
Supplier6
Terminal7
Product5
Location11
Manager3
Customer2
Price
2
6/6/18
12:01 AM
Supplier7
Terminal16
Location13
Location15
Manager3
Customer2
Price
1
6/5/18
6:00 PM

<tbody>
</tbody>
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
How about
Code:
Sub DelRows()
   Dim ary As Variant
   Dim Nary As Variant
   Dim r As Long, c As Long, j As Long
   ary = Range("A1").CurrentRegion
   ReDim Nary(1 To UBound(ary), 1 To UBound(ary, 2))
   For r = 1 To UBound(ary)
      If Not IsEmpty(ary(r, 5)) Then
         j = j + 1
         For c = 1 To UBound(ary, 2)
            Nary(j, c) = ary(r, c)
         Next c
      End If
   Next r
   Range("A1").CurrentRegion.Clear
   Range("A1").Resize(j, UBound(Nary, 2)).Value = Nary
End Sub
 
Upvote 0
If you can really get away with just looking at column E, then this one line of VBA code should do that:
Code:
    Columns("E:E").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
 
Last edited:
Upvote 0
How about
Code:
Sub DelRows()
   Dim ary As Variant
   Dim Nary As Variant
   Dim r As Long, c As Long, j As Long
   ary = Range("A1").CurrentRegion
   ReDim Nary(1 To UBound(ary), 1 To UBound(ary, 2))
   For r = 1 To UBound(ary)
      If Not IsEmpty(ary(r, 5)) Then
         j = j + 1
         For c = 1 To UBound(ary, 2)
            Nary(j, c) = ary(r, c)
         Next c
      End If
   Next r
   Range("A1").CurrentRegion.Clear
   Range("A1").Resize(j, UBound(Nary, 2)).Value = Nary
End Sub

How do I name the sheet as "Data". I am running this macro from a button on a different sheet.
 
Upvote 0
Is the sheet already called Data?
If not what is it's current name?
 
Upvote 0
Sheet name is already named data.

I just tested the previous code. The eff.time is being reformatted from 6:00 PM to .75.

Thanks for the quick response.

<tbody>
</tbody><colgroup><col></colgroup>
 
Upvote 0
OK, how about
Code:
Sub DelRows()
   Dim ary As Variant
   Dim Nary As Variant
   Dim r As Long, c As Long, j As Long
   ary = Sheets("Data").Range("A1").CurrentRegion
   ReDim Nary(1 To UBound(ary), 1 To UBound(ary, 2))
   For r = 1 To UBound(ary)
      If Not IsEmpty(ary(r, 5)) Then
         j = j + 1
         For c = 1 To UBound(ary, 2)
            Nary(j, c) = ary(r, c)
         Next c
      End If
   Next r
   With Sheets("Data")
      .Range("A1").CurrentRegion.Clear
      .Range("A1").Resize(j, UBound(Nary, 2)).Value = Nary
      .Range("J:J").NumberFormat = "h:mm AM/PM"
   End With
End Sub
 
Upvote 0
OK, how about
Code:
Sub DelRows()
   Dim ary As Variant
   Dim Nary As Variant
   Dim r As Long, c As Long, j As Long
   ary = Sheets("Data").Range("A1").CurrentRegion
   ReDim Nary(1 To UBound(ary), 1 To UBound(ary, 2))
   For r = 1 To UBound(ary)
      If Not IsEmpty(ary(r, 5)) Then
         j = j + 1
         For c = 1 To UBound(ary, 2)
            Nary(j, c) = ary(r, c)
         Next c
      End If
   Next r
   With Sheets("Data")
      .Range("A1").CurrentRegion.Clear
      .Range("A1").Resize(j, UBound(Nary, 2)).Value = Nary
      .Range("J:J").NumberFormat = "h:mm AM/PM"
   End With
End Sub

That worked like a champ!!! thank you so much. I am still learning vb and you are very helpful.
 
Upvote 0
Glad we could help & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top