Macro to Delete blank rows in a RANGE

[JoeyMang83]

Hello,

I am having trouble finding (or creating working) code to work within an existing sub routine.

I simply need to delete blank rows within this range (B300:B1000), but neither of the code snippets below work? Can you help? Thank you.

Range("B300:B1000").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

Dim LastRow As Integer 
Dim i As Integer 
LastRow = ActiveSheet.Range("B1000").End(xlUp).Row 
For i = LastRow To 300 Step -1 
    If ActiveSheet.Cells(i, 2).Value = "" Then 
        ActiveSheet.Cells(i, 2).EntireRow.Delete 
    End If 
Next i

THANK YOU SO MUCH!!!

 

[rs2k]

See if this helps;

Sub RemoveEmptyRows()
On Error Resume Next
    Range("B300:B1000").Select
    Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

HTH
Colin

 

[JoeyMang83]

Thank you Colin! Your code works if I try it in a new separate spreadsheet (as a test). For some reason, it won't work in the intended sheet that I have, and it may have something to do with the fact that the range of values were pasted (as .value) from another function.

I can clearly see 20 blank rows (no data, no formulas, no "", nothing at all), but the code will not delete those rows after they've been pasted into the range as a .value (even if that .value is nothing).

Does the code need to change to accomodate this? I assume that's what it is? Thank you.

 

[erik.van.geit]

Hi,
This will probably help.
It will first convert the "pseudo blanks" into real blanks.

Sub RemoveEmptyRows()
On Error Resume Next
    With Range("B300:B1000")
    .Value = .Value
    .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    End With
End Sub

kind regards,
Erik

EDIT: by the way: no need to "select" anything

 

[JoeyMang83]

Brilliant Erik! Works like a charm! Thank you so much!

Have a great one.

 

[erik.van.geit]

You are welcome!
Have a nice last day of the week & weekend

 

[BJLong76]

Newbie here with a question - This is my very first post, please excuse me if I not following the proper forum ettiquette. First, I did find the code above to be very helpful. Thank you.

Question - However, to expand on the formula above, how would it need to be coded to remove blank rows within a selected range only; the selection would change based upon need and would not be the entire sheet row? Thank you in advance.

 

[erik.van.geit]

Hello,
WELCOME to the Board!

After about one week, you still didn't have any reply. This is because you posted within an old thread.
You would better start your own thread; more people will notice your question, even after a few days (some members are looking in unanswered questions). If you want, you can always refer to this one.
kind regards,
Erik

 

[TayoMayo]

Hi all,
I am extremely new to <acronym title="visual basic for applications">VBA</acronym>, and am trying to program a macro that will delete an entire row based on a range of cells in that row. For example, I need to delete the entire row if cells A3, B3, and C3 are all blank. The code I have pieced together is below. The issue arises on the bold line. As best I can tell, the issue arises with the .Range() part of the line. I get an error that says there is a 'type mismatch' . I'm not entirely sure what that means or how to go about fixing it. Any and all help is greatly appreciated.

Sub DeletingBlankRows()
'Deletes the entire row if certain cells are completelly blank

Worksheets("Sheet1").Activate
Range("A1").Select
Range("A1").Activate
Dim deleter As Long
    With Application
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
        
        For deleter = Selection.Rows.Count To 65 Step 1
        
            If ActiveCell.Offset(1, 0) = ActiveCell.Offset(0, 0) Then
            
                ActiveCell.Offset(1, 0).Select
                Selection.Activate
                
                    If ActiveCell.Offset(0, 3).Range("A1:D3") = "" Then
                    
                        Selection.Rows(deleter).EntireRow.Delete
                    
                    Else
                    
                        ActiveCell.Offset(1, 0).Select
                        Selection.Activate
                
                    End If
                    
            Else
            
                ActiveCell.Offset(1, 0).Select
                Selection.Activate
                
            End If
            
        Next deleter
        
    End With
        
End Sub

 

[NeonRedSharpie]

This...this is over 2 years old. Think about making your own separate post.