Excel 2010 VBA Code to Paste-As-Values a block of columns one-by-one I need to c

Question

Excel 2010 VBA Code to Paste-As-Values a block of columns one-by-one

I need to copy B1:BZ100 as values, ending up in the same B1:BZ100 location (actually, the end row is a dynamic value - call it LastRow). BZ is not the last column in the sheet, either.

Due to the complexity and nature of the formulas in this range, I cannot copy the entire block in one copy-->paste-as-values step, nor can I process the block row by row. I need to perform the transformation column by column.

I would like to utilize the following construct (in English -- the syntax to code this is my problem), and R1C1 referencing of the columns seems to be the prudent approach.

For CopyCol = 2 to 78 ' Column 78 = Column BZ

   Range(R1C<CopyCol> : RLastRowC<CopyCol>).Copy

   Range(R1C<CopyCol> : RLastRowC>CopyCol>).PasteSpecial Paste:=xlPasteValues

Next CopyCol

If LastRow = 100, I would be processing B1:B100, C1:C100, D1:D100, E1:E100, ..., BY1:BY100, BZ1:BZ100.

I simply can't seem to get the correct R1C1-flavored syntax to refer to each column being processed.

If there's an A1-style solution, that's fine, too !!

Thanks !!

Explanation / Answer

Hi..

Does this work for you?

Sub Test1()
    Dim m As Long
    Application.ScreenUpdating = False
    m = Range("B:BZ").Find(What:="*", SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious).Row
    With Range("B1:BZ" & m)
        .Value = .Value
    End With
    Application.ScreenUpdating = True
End Sub

If not, try this:

Sub Test2()
    Dim m As Long
    Dim c As Range
    Application.ScreenUpdating = False
    m = Range("B:BZ").Find(What:="*", SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious).Row
    For Each c In Range("B1:BZ" & m).Columns
        c.Value = c.Value
    Next c
    Application.ScreenUpdating = True
End Sub

Related Questions

Navigate

• Browse (All)
• Browse E
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.