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
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