• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Removing only entire blanks row in a sheet.

R

Ramesh Deo

Member
What methods to remove only entire blanks row not any blanks cell in a sheet if we have multiples data in a sheet, any keyboard shortcuts????
 
If you want to eliminate all blank rows in a data set
Sort it
The blanks will go to the top/Bottom and you can then delete them
 
Hi,

"BlankRows" removes the row which DO NOT have any data in the cell in column A
"AllBlankRows_Delete" deletes the row where you have your cursor i.e. cell C8 is active so row 8 will be deleted

Hope this helps,
Code: 
Sub BlankRows()
'We turn off calculation and screenupdating to speed up the macro.
    With Application
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    .Calculation = xlCalculationAutomatic
        .ScreenUpdating = True
    End With
End Sub
Sub AllBlankRows_Delete()

'Deletes the entire row within the selection if the ENTIRE row contains no data.

'We use Long in case they have over 32,767 rows selected.

Dim i As Long

    'We turn off calculation and screenupdating to speed up the macro.
    With Application
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
   
    'We work backwards because we are deleting rows.
    For i = Selection.Rows.Count To 1 Step -1

        If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then
            Selection.Rows(i).EntireRow.Delete
        End If
    Next i
        .Calculation = xlCalculationAutomatic
        .ScreenUpdating = True

    End With

End Sub
 
Hi Ramesh

you can select what rows or columns you that contain the blanks cell that you want to remove. then from Home ribbon choose Find & Select option, then click Go To Special... and from pop out window select Blanks
finally delete the selected data

thanks,
 
Afarag said:
Hi Ramesh

you can select what rows or columns you that contain the blanks cell that you want to remove. then from Home ribbon choose Find & Select option, then click Go To Special... and from pop out window select Blanks
finally delete the selected data

thanks,
Click to expand...
Hi, Afarag, I think this method cann't be used for multiple Data.its a manual method.have u any method to select only entire blank rows through shortcuts,if any plz let me know!!!
 
Chris Macro said:
Funny thing, I just released a blog post about how to do this with VBA a few hours ago! You can check out the code snippet in my post Delete Blank Cells Or Entire Blank Rows Within A Range. Let me know if you have any questions about how to implement it but it should be pretty straight forward.
Click to expand...
Thanks Chris
I didn't know about dis posting, but Its working n helpful for me & all.
let me know chris about dis Range("A1:A10") in macro, can we increase this range and till what range???
 
Ramesh Deo said:
Thanks Chris
I didn't know about dis posting, but Its working n helpful for me & all.
let me know chris about dis Range("A1:A10") in macro, can we increase this range and till what range???
Click to expand...

Yes you can expand the range to what ever length you wish.
 
Yes, you could put it inside something like this:

Code: 
Sub AllOpenWBs()

Dim wb As Workbook
Dim sht As Worksheet
Dim rng As Range

'Loop through all workbooks
For Each wb In Application.Workbooks
  'Loop through all sheets in workbook
    For Each sht In wb.Worksheets
     
      Set rng = sht.Range("A1:A10").SpecialCells(xlCellTypeBlanks)
      rng.Rows.Delete Shift:=xlShiftUp
   
    Next sht
Next wb

End Sub
 
Chris Macro said:
Yes, you could put it inside something like this:

Code: 
Sub AllOpenWBs()

Dim wb As Workbook
Dim sht As Worksheet
Dim rng As Range

'Loop through all workbooks
For Each wb In Application.Workbooks
  'Loop through all sheets in workbook
    For Each sht In wb.Worksheets
    
      Set rng = sht.Range("A1:A10").SpecialCells(xlCellTypeBlanks)
      rng.Rows.Delete Shift:=xlShiftUp
  
    Next sht
Next wb

End Sub
Click to expand...


I mean to say whenever I open a new workbook den can this work??
 
Are you wanting to make it function as a personal macro and run it on the fly in whatever workbook you have open? This would definitely be possible if I am understanding you correctly.
 
I would code it like this then:

Code: 
Sub DeleteBlanks()

Dim rng As Range
 
Set rng = Activesheet.Range("A1:A10").SpecialCells(xlCellTypeBlanks)
rng.Rows.Delete Shift:=xlShiftUp

End Sub
 
You must log in or register to reply here.
Back
Top