VBA delete blank rows

[JoeyGaspard]

I am trying to use the code below to delete the entire row if the corresponding cell in Column B is empty. It works only if there is nothing in the other columns, such as, if B5 is blank, but G5 is not, it doesnt delete the row? Thanks in advance.

'Delete Blank Rows if No Data in cells in Column B
On Error Resume Next
Sheets("GeneralJournal").Select
Range("B17:B500").Select
Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Range("B17").Select

 

[JoeyGaspard]

Glad we could help & thanks for the feedback

Fluff, I am encountering the same issue again. I have 2 macros that are identical, 1 is for Business Unit 03, and the other is for Business Unit 04, they are identical except for which sheets they run on and the data they process, it works perfectly on the macro for 03, but when I run the macro for 04, it adds about 400+ lines of blank data which will bomb the upload I am trying to do?? Can I send you my spreadsheet and let you take a look at it, I am at my wits end here.....sorry for the trouble in advance

 

[Zack Barresse]

To specify the sheet, it would be...

With Sheets("GeneralJournal").Range("B17:B500")
   .Value = .Value
   .SpecialCells(xlBlanks).EntireRow.Delete
End With

... which is better than relying on the ActiveSheet anyway.

 

[Fluff]

Maybe

With Range("B17:B500")
   .Value = .Value
   .SpecialCells(xlBlanks).EntireRow.Delete
   .Parent.UsedRange
End With

 

[JoeyGaspard]

Here is the code I use in both macros: It deletes the blank rows for one, but not the other?


On Error Resume Next
Sheets("GeneralJournal").Select
With Range("B17:B500")
.Value = .Value
.SpecialCells(xlBlanks).EntireRow.Delete
End With
Range("B17").Select

 

[Fluff]

In that case can you supply the file?
To do so, you will need to upload it to a share site such as OneDrive, DropBox. Mark it for sharing & post the link.

 

[JoeyGaspard]

In that case can you supply the file?
To do so, you will need to upload it to a share site such as OneDrive, DropBox. Mark it for sharing & post the link.

 

[JoeyGaspard]

https://houseofraeford-my.sharepoin...tChsFa26k5dCEBpvRbLjvE_6E5cOBkprw5aA?e=nJSVmC

Sorry, bad link above, try this one instead

Upload Template - Old Harris.xlsm

Shared with Dropbox

www.dropbox.com

 

[Fluff]

Thanks for that.
The problem is that your data is in a table & it doesn't let you delete entire rows if they are not contiguous.
Try

With Sheets("GeneralJournal").ListObjects("Table1").ListColumns("Main account").DataBodyRange
   .Value = .Value
   .SpecialCells(xlBlanks).Delete
End With
Range("B17").Select

 

[JoeyGaspard]

Thanks for that.
The problem is that your data is in a table & it doesn't let you delete entire rows if they are not contiguous.
Try

With Sheets("GeneralJournal").ListObjects("Table1").ListColumns("Main account").DataBodyRange
   .Value = .Value
   .SpecialCells(xlBlanks).Delete
End With
Range("B17").Select

Thank you so much! As always, your help is greatly appreciated.

 

[Fluff]

Glad we could help & thanks for the feedback.