How to resize a range of multiple cells?

[RobinMO01]

I have a spreadsheet with data in cells A1:AR1000. Some of the cells are empty. I want to SELECT all the empty cells in row “H”. I then want to DELETE all the empty cells PLUS the five cells to the left of each of the selected cells. In the attached example the macro selects the following cells: H10, H11, H12, H20, H21, H31, H32. Based on the sells now found as empty in row “H” I now want to extend the range for each selected cell. UNFORTUNATELY MY CODE APPEARS TO BE WRONG. At this point I want the macro to select the following cells:
H10:C10, H11:C11, H12:C12, H20:C20, H21:C21, H31:C31, and H32:C:32. But sadly the range for the selected cells does not change. Please help.
Note I will always search for empty cells in Column “H” and always resize the range for deletion from column H to Column C. I do not want to delete the entire rows because to do so would result in deleting cells that I needed. The code is:

Please Login or Register  to view this content.

[nilem]

Hi RobinMO01,
try this

Please Login or Register  to view this content.

[RobinMO01]

Hi nilem,
Thank you that is exactly what I needed. You are my hero. I worked hours unsuccessfully trying to get that to work. Thank you and may God bless you!!
Robin

[sktneer]

If that takes care of your original question, please mark your thread as Solved by selecting Thread Tools (just above your first post) --> Mark thread as solved.

You may also say thanks to those who have put their time and efforts to help you in this forum by clicking the Add Reputation link under their posts, another way to say thanks.