EXCEL: How do I ignore several header rows while looping through columns to change number format?

The code below is working. It will progress through all columns in the sheet and change the data within it to a number of fixed length based on the number found in the 2nd row.

My issue is that it selects the entire column when doing so. This is a problem for me since I have 4 header rows that I do not want converted.

My first thought was to offset/resize a selection and apply changes to all cells, but I'm simply having no luck doing that.

Can anyone modify this code to ignore the first 4 header rows as it progresses through the columns?

Note: lastCol is a separate function that simply returns an integer value with the number of the last used column on the sheet.

Sub FormatFixedNumber()

    Dim i As Long

    Application.ScreenUpdating = False

    For i = 1 To lastCol 'replace 10 by the index of the last column of your spreadsheet
        With Columns(i)
            .NumberFormat = String(.Cells(2, 1), "0") 'number length is in second row
        End With
    Next i
    Application.ScreenUpdating = True

End Sub

2012-04-04 03:20
by toolshed

This should do it. I added a Constant to hold the header rows count.

EDIT: Added code to just go to last row as requested. Also checks that LastRow is greater than HEADER_ROWS. And fixed some convoluted adding and subtracting of the HEADER_ROWS in the Resize/Offset.

Sub FormatFixedNumber()
Const HEADER_ROWS As Long = 4
Dim i As Long
Dim LastRow As Long

Application.ScreenUpdating = False
For i = 1 To LastCol    'replace 10 by the index of the last column of your spreadsheet
    With Columns(i)
        LastRow = .Cells(Rows.Count).End(xlUp).Row
        If LastRow > HEADER_ROWS Then
            With .Resize(LastRow - HEADER_ROWS).Offset(HEADER_ROWS)
                .NumberFormat = String(.EntireColumn.Cells(2, 1), "0")    'number length is in second row
            End With
        End If
    End With
Next i
Application.ScreenUpdating = True
End Sub

2012-04-04 03:34
by Doug Glancy

This works perfectly except that it selects the entire column. Is it possible to modify this to simply go to the last row? I was trying out UsedRange.Rows, but having no luck - toolshed 2012-04-04 04:06

Getting an overflow error - toolshed 2012-04-04 05:00

woops found a typo - the old Row not Rows. Should have been End(xlup).Row. Fixed now - Doug Glancy 2012-04-04 05:10

That works great! Thanks Doug - toolshed 2012-04-04 23:23