Macro for ClearContents, but not All Contents

[Dr Dee]

Hi all,
I'm using a macro to remove the contents of rows but only want to clear from Column C on. Have been pulling my hair out over this one.
The macro reads -

Sub Macro1()
Range("C240").Select
ActiveCell.FormulaR1C1 = "End"

Range("C4:C250").Select
Do Until ActiveCell.Value = "End"
If ActiveCell.Value = "" Then
ActiveCell.Offset(0, 0).Select
ActiveCell.EntireRow.ClearContents

End If
ActiveCell.Offset(1, 0).Select
Loop
Range("C240").Select
Selection.ClearContents

End Sub

This clears the contents of the whole row from 4 to 240 if Cell C# is blank, but I want columns A & B to stay as is.

Thanks guys,
Den

 

[Rick Rothstein]

The intention of your code is a little unclear... you put "End" in cell C240 and the search the range C2:C250 (which ten cells below C240) for the word "End". Why are you putting "End" in C240? Is that a cell past the end of your data or is there some other reason for you choosing it? Anyway, just guessing here, but does this code clear the cells you want? If not, tell us why not...

Sub ClearFromColumCifCisBlank()
  Intersect(Columns("C").SpecialCells(xlCellTypeBlanks).EntireRow, Columns(3).Resize(, Columns.Count - 2)).Clear
End Sub

 

[repairman615]

Hello,

Welcome to the Board!

Here is an example that uses a different approach. I am not sure how to dim a small range into a variable array... really not sure if that is even possible. Honestly, I was not even sure how to copy and paste with a .entirerow.clear betwen the two. without referencing forms and using .putinclipboard



<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Macro1()<br><SPAN style="color:#00007F">Dim</SPAN> cell <SPAN style="color:#00007F">As</SPAN> Range<br><SPAN style="color:#00007F">Dim</SPAN> var1<br><SPAN style="color:#00007F">Dim</SPAN> var2<br><SPAN style="color:#00007F">Dim</SPAN> var3<br><br><br>Range("C240").Value = "End"<br><br><SPAN style="color:#007F00">'Range("C4:C250").Select</SPAN><br><SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> cell <SPAN style="color:#00007F">In</SPAN> Range("C4:C250")<br>    <SPAN style="color:#00007F">If</SPAN> cell.Value <> "End" <SPAN style="color:#00007F">Then</SPAN><br>        var1 = Range("A" & cell.Row).Value<br>        var2 = Range("B" & cell.Row).Value<br>        var3 = Range("C" & cell.Row).Value<br>        <br>        <br>        cell.EntireRow.Clear<br>        Range("A" & cell.Row).Value = var1<br>        Range("B" & cell.Row).Value = var2<br>        Range("C" & cell.Row).Value = var3<br>        <br>    <SPAN style="color:#00007F">Else</SPAN><br>        <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">For</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">Next</SPAN> cell<br><br>Range("C240").Clear<br>        <br>    <br><br><SPAN style="color:#007F00">'Do Until ActiveCell.Value = "End"</SPAN><br><SPAN style="color:#007F00">'If ActiveCell.Value = "" Then</SPAN><br><SPAN style="color:#007F00">'ActiveCell.Offset(0, 0).Select</SPAN><br><SPAN style="color:#007F00">'ActiveCell.EntireRow.ClearContents</SPAN><br><SPAN style="color:#007F00">'</SPAN><br><SPAN style="color:#007F00">'End If</SPAN><br><SPAN style="color:#007F00">'ActiveCell.Offset(1, 0).Select</SPAN><br><SPAN style="color:#007F00">'Loop</SPAN><br><SPAN style="color:#007F00">'Range("C240").Select</SPAN><br><SPAN style="color:#007F00">'Selection.ClearContents</SPAN><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[mikerickson]

With ActiveSheet.UsedRange
    .Cells(1,3).Resize(.Rows.Count, .Columns.Count - 3).ClearContents
End With

 

[Dr Dee]

Thanks Rick,
getting close.

Can we start from Column C, Row 4 and without clearing formatting?
Rows 1-3 have unrelated headers, subtotals etc.
Otherwise great.
(the C250 was a typo...Oops )


Repairman,
your formula clears everything east of column C
It has certainly given me other ways to look at things though

Thanks,
Den

 

[mikerickson]

With ActiveSheet
    .Cells(4,3).Resize(.UsedRange.Rows.Count - 4, .UsedRange.Columns.Count - 3).ClearContents
End With

 

[Rick Rothstein]

Thanks Rick,
getting close.

Can we start from Column C, Row 4 and without clearing formatting?
Rows 1-3 have unrelated headers, subtotals etc.
Otherwise great.
(the C250 was a typo...Oops )

Okay, still not clear if you want everything from C4 downward to be cleared or only to Row 240, so I'll still assume everything...

Sub ClearFromColumCifCisBlank()
  Intersect(Range("C4:C" & Cells(Rows.Count, "C").Row).SpecialCells(xlCellTypeBlanks).EntireRow, Columns(3).Resize(, Columns.Count - 2)).ClearContents
End Sub

If you really meant to stop at Row 240 and not clear anything below it, then use this instead..

Sub ClearFromColumCifCisBlank()
  Intersect(Range("C4:C240").SpecialCells(xlCellTypeBlanks).EntireRow, Columns(3).Resize(, Columns.Count - 2)).ClearContents
End Sub

 

[Dr Dee]

Thanks Rick,
perfect.

The 240 was just an arbitrary number I picked after which there will be no data and to facilitate the macro -

Range("A240").Select
ActiveCell.FormulaR1C1 = "End"

Range("A4:A240").Select
Do Until ActiveCell.Value = "End"
If ActiveCell.Value = 1 Then
ActiveCell.Offset(1, 0).Select
ActiveCell.EntireRow.Insert
End If
ActiveCell.Offset(1, 0).Select
Loop

which takes proportionately longer to work the higher the row number.


Mike,
both your suggestions delete everything from column c right.