VBA code to delete blank rows in a range when the range is different each month

sassy2

New Member
Joined
Dec 9, 2018
Messages
4
I have a workbook that has 2 sheets. The first one contains the current month's activity and at the end of the month that is added to the end of sheet 2 which contains activity for each month of the year. Everything is working great until the last few lines of the code where I am trying to delete the blank lines that were copied over to sheet 2, but since the range changes each month because I'm adding to sheet 2 each time, I don't know how to choose the range and delete the blank lines.

Thank you in advance for any help you can offer.

Sassy2
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
You haven't given us ALL the required info, but this will delete all rows that have a blank in Column "A"

Code:
Sub MM1()
  With Range("A1", Cells(Rows.Count, "A").End(xlUp))
    .Replace "", "#N/A", xlWhole, , False, , False, False
    Columns("A").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
  End With
End Sub
 
Upvote 0
Thank you for your response. Sorry I didn't provide enough information, this is all new to me including writing macros that are more than simple print macros, etc. I tried the code you provided but it deletes all blank rows on the sheet not just the blank rows in the range I'm working with at the time.

The purpose of this workbook is at the end of each month the first sheet which contains information on the current month sales is copied to the bottom of sheet 2 which contains the data for each month of the year. There are some blank rows in the ranges for the previous months which have already been copied over to sheet 2 that I want to stay.

Thank you for any help you can provide.
 
Upvote 0
Well, if you provided the ranges your working with we may still be able to help
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,287
Members
448,562
Latest member
Flashbond

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