Find blank cells and clear adjacent range

  • Thread starter Thread starter rbel
  • Start date Start date
R

rbel

While I know how to find blank cells and delete complete rows, how do I
find all the blank cells in column C and clear the cell contents of the
next 5 cells to the right?

Grateful for some advice.
 
Have a look in the vba help index for RESIZE

Sub resizetoright()
C.resize(, 5).clearcontents
End Sub
 
While I know how to find blank cells and delete complete rows, how do I
find all the blank cells in column C and clear the cell contents of the
next 5 cells to the right?

Grateful for some advice.
Click to expand...

Assuming your code is actually looping through and selecting the cells
in column C, use the same same code but instead of deleting the entire
row just do this instead:

Range("D" & ActiveCell.Row & ":H" & ActiveCell.Row).ClearContents
 
I think this should do what you want...

Sub ClearFiveRight()
Dim X As Long
For X = 1 To Me.UsedRange.Rows.Count
If Cells(X, "C").Value = "" Then
Cells(X, "C").Resize(1, 6).ClearContents
End If
Next
End Sub

Rick
 
By the way, I do realize that UsedRange can be larger than the actual grid
of data; I used it to put a realistic limit on the number of iterations the
loop would have to process.

Rick
 
Rick - thanks for the code however it is producing an 'invalid use of Me
keyword' warning.
 
Where did you put the code at? If I right-click the worksheet tab (for the
sheet I want to run the code on) and select View Code from the popup menu
that appears, then copy/paste the code I posted into the code-window that
appears, then it works fine for me (I can either run it directly within the
VBA editor where you pasted the code or from the worksheet itself by
pressing Alt+F8).

Rick
 
Indeed it does work - out of habit I had put it in a module.
Thank you

As a matter of interest what changes to your code would be necessary to
get it to run in a module?

rbel
 
It should go into a module IMO

Leave sheet modules for event code.

Change Me to ActiveSheet


Gord Dibben MS Excel MVP
 
Back
Top