Basic Fixed Loop Macro

[Will85]

I am trying to learn loops.

My vba code is:

Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Selection.End(xlDown).Select
Selection.End(xlDown).Select

I would like to repeat this 5x

Or

Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Selection.End(xlDown).Select
Activecelloffset(0, -1) <<<<if this="" equals="" "x"="" then="" stop="" the="" loop,="" if="" not="" then
IF THIS CELL = "X" THEN STOP THE LOOP, IF NOT THEN . . .
Activecelloffset(0, 1) <<<< TO GET BACK TO WHERE WE LEFT OFF BEFORE TESTING
Selection.End(xlDown).Select

Then continue the loop until we find an "X"</if>

 

[Will85]

To be clearer, I really need:

Selection.End(xlDown).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Selection.End(xlDown).Select
Activecelloffset(0, -1) <<<<if this="" equals="" "x"="" then="" stop="" the="" loop,="" if="" not="" then
IF THIS CELL = "X" THEN STOP THE LOOP, IF NOT THEN . . .
Activecelloffset(0, 1) <<<< TO GET BACK TO WHERE WE LEFT OFF BEFORE TESTING


Then continue the loop until we find an "X"</if>

 

[Will85]

Im now also realizing that my xlToRight repeated four times doesnt work, it only works once. Need to find a workaround for that.

 

[Michael M]

Have you looked at .CurrentRegion or .UsedRange instead of your current approach ??

Activesheet.currentregion.clear

OR

Activesheet.usedrange.clear

You don't need to use Select to clear data !!

 

[Will85]

Not sure I follow. My variable range is five columns accross, I need to select all five columns, except they are seperated by a single blank column between each.

 

[Michael M]

Current region will clear the area within the "blank rows / coloumns
Usedrange will clear all within the usedrange regardless of blank rows / columns

 

[Michael M]

I'm still not quite clear what you are trying to achieve, but this might help

It assumes the entire row can be cleared...
It also assumes that the "x" will be in column "A".
It starts at row 2 and works down the page

Sub MM1()
Dim lr As Long, r As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
For r = 2 To lr
    If Cells(r, 1) <> "x" Then
        Rows(r).Clear
        Else
        Exit Sub
    End If
Next r
end sub

Rather than posting code that doesn't work and is meaningless to others.....it might be easier to simply explain in words what you are trying to achieve !