Find blank cells and clear adjacent range
 

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.
--
rbel

Find blank cells and clear adjacent range
 

Have a look in the vba help index for RESIZE

Sub resizetoright()
C.resize(, 5).clearcontents
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"rbel" wrote in message
...

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.
--
rbel

Find blank cells and clear adjacent range
 

On Dec 11, 12:12 pm, rbel wrote:
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.
--
rbel

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

Find blank cells and clear adjacent range
 

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


"rbel" wrote in message
...

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.
--
rbel

Find blank cells and clear adjacent range
 

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 Rothstein (MVP - VB)" wrote in
message ...
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


"rbel" wrote in message
...

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.
--
rbel

Find blank cells and clear adjacent range
 

Rick - thanks for the code however it is producing an 'invalid use of Me
keyword' warning.



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


"rbel" wrote in message
...

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.
-- rbel


--
Robert

Find blank cells and clear adjacent range
 

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


"Robert" wrote in message
...

Rick - thanks for the code however it is producing an 'invalid use of Me
keyword' warning.



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


"rbel" wrote in message
...

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.
-- rbel


--
Robert

Find blank cells and clear adjacent range
 

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

rbel

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


"Robert" wrote in message
...

Rick - thanks for the code however it is producing an 'invalid use of
Me keyword' warning.



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


"rbel" wrote in message
...

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.
-- rbel

Find blank cells and clear adjacent range
 

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


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


"Robert" wrote in message
...

Rick - thanks for the code however it is producing an 'invalid use of
Me keyword' warning.



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


"rbel" wrote in message
...

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.
-- rbel


-- Robert


--
Robert

Find blank cells and clear adjacent range
 

It should go into a module IMO

Leave sheet modules for event code.

Change Me to ActiveSheet


Gord Dibben MS Excel MVP

On Tue, 11 Dec 2007 22:47:28 +0000, Robert
wrote:


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


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


"Robert" wrote in message
...

Rick - thanks for the code however it is producing an 'invalid use of
Me keyword' warning.



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


"rbel" wrote in message
...

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.
-- rbel


-- Robert