CODEWITHSUNDEEP
vbaexcel
chackmann97
chackmann97

Reputation: 21

Macro to select and range, change format, offset then loop

I have an 2010 Excel sheet with essentially 80 identical "pages" 30 rows each. There is a range of cells starting with "K126:N130" which I need to turn off text wrapping for this range, then repeat for the following 79 "pages" and stop at row 2520 I am unfamiliar with "do until" but here is what I have so far. Thanks

Sub formatchange()
'
' formatchange Macro
'
Range("K126:N130").Select
With Selection
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
End With
ActiveSheet.Range("K126:N130").Offset(30).Select
With Selection
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
End With
End Sub

Upvotes: 2

Views: 290

Answers (1)

Tim Williams
Tim Williams

Reputation: 166381

Try this:

Sub formatchange()

    Dim rng As Range

    Set rng = ActiveSheet.Range("K126:N130")

    do while rng.cells(1).row < 2520

    With rng
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
    End With

    Set rng = rng.offset(30,0)

    Loop

End Sub

Upvotes: 1

Related Questions