Copy column widths and row heights

With the macros below you can copy column widths and row heights from one range to another.

Private Sub CopyRowHeigths(TargetRange As Range, SourceRange As Range)
Dim r As Long
    With SourceRange
        For r = 1 To .Rows.Count
            TargetRange.Rows(r).RowHeight = .Rows(r).RowHeight
        Next r
    End With
End Sub

Private Sub CopyColumnWidths(TargetRange As Range, SourceRange As Range)
Dim c As Long
    With SourceRange
        For c = 1 To .Columns.Count
            TargetRange.Columns(c).ColumnWidth = .Columns(c).ColumnWidth
        Next c
    End With
End Sub

Example:

CopyColumnWidths(Range("E1:H1"), Range("A1:D1"))
CopyColumnWidths(Worksheets("Sheet2").Range("A1:D1"), _
    Worksheets("Sheet1").Range("A1:D1"))

Document last updated 1999-12-20 12:51:27 Printer friendly version