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