Macro to format a sheet with variable # of rows

[Alex881]

I'm new to Macros, but I'm trying to write one that does the same formatting (e.g., bolds columns, adds a total, adds a column, etc) regardless of the number of rows. The data I'm working with will have a different number of rows each time, but the columns remain the same. I was wondering how to go about writing one. I've used the record macro feature with relative references enabled, but I can only get decent results using data that has the exact same number of rows. If I change the data and the number of rows I'm working with, the macro doesn't work properly. Maybe there's a tutorial for this sort of thing? That would help me out a lot.

Thanks!

 

[DanteAmor]

Welcome to the forum

You can put your macro to make the adjustments and explain each of them.

 

[Alex881]

Sub tryn()
'
' tryn Macro
'
'
    ActiveCell.Cells.Select
    ActiveCell.Cells.EntireColumn.AutoFit [B]<--HOW DO I MAKE THE MACRO AUTOFIT EACH OF THE COLUMNS?[/B]
    ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select
    ActiveWindow.FreezePanes = True [B]<--HOW DO I MAKE SURE IT FREEZES THE TOP ROW OF THE SPREADSHEET EVERY SINGLE TIME?[/B]
    ActiveCell.Offset(0, 14).Columns("A:A").EntireColumn.Select
    Selection.Delete Shift:=xlToLeft
    ActiveCell.Offset(0, -2).Columns("A:A").EntireColumn.Select
    Selection.Delete Shift:=xlToLeft
    ActiveCell.Offset(0, -4).Columns("A:A").EntireColumn.Select
    Selection.Delete Shift:=xlToLeft
    ActiveWindow.ScrollColumn = 1
    ActiveCell.Offset(1, 0).Range("A1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(0, -1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "TOTAL" [B]<-- HERE I AM TRYING TO ADD 'TOTAL' NEXT TO THE BOTTOM OF THE SPREADSHEET'S DATA (TO THE LEFT OF THE ACTUAL TOTAL THE SS GENERATES[/B]
    ActiveCell.Range("A1:B1").Select
    ActiveCell.Offset(0, 1).Range("A1").Activate
    Selection.Font.Bold = True
    With Selection.Font
        .Color = -16776961
        .TintAndShade = 0
    End With
    ActiveCell.Offset(-1, 0).Range("A1").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    ActiveWindow.SmallScroll Down:=3
    ActiveCell.Offset(1, 0).Range("A1").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlDouble
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThick
    End With
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    ActiveCell.Offset(-66, 4).Range("A1").Select
    ActiveCell.FormulaR1C1 = "COMMENTS"
    Selection.Font.Bold = True
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveCell.Columns("A:A").EntireColumn.EntireColumn.AutoFit
    ActiveCell.Cells.EntireColumn.AutoFit
    Range("A2").Select
End Sub

I'm really bad at this Macro stuff - sorry - this is from a recording.

 

[DanteAmor]

It is difficult to follow this macro because I do not know in what cell you started.
Better tell me what you need to do with each column.


I give you a small sample:

Write the letter of the column you want to delete (the first column must be the largest and so on)

Sub tryn()
' tryn Macro
    Cells.EntireColumn.AutoFit          '<-- AUTOFIT all columns
    ActiveWindow.FreezePanes = False
    Range("A2").Select
    ActiveWindow.FreezePanes = True     '<--Freeze row 1
    
    Columns("[COLOR=#ff0000]Z[/COLOR]").EntireColumn.Delete Shift:=xlToLeft    '<-- Write the letter of the column you want to delete
    Columns("[COLOR=#ff0000]J[/COLOR]").EntireColumn.Delete Shift:=xlToLeft    '<-- Write the letter of the column you want to delete
    Columns("[COLOR=#ff0000]H[/COLOR]").EntireColumn.Delete Shift:=xlToLeft    '<-- Write the letter of the column you want to delete
    
    Range("[COLOR=#ff0000]D[/COLOR]" & Rows.Count).End(xlUp)(2).Value = "TOTAL" '<--ADD 'TOTAL' TO THE BOTTOM of column D
    
End Sub

 

[Alex881]

Thanks for your assistance, Dante.

The document exports with 15 columns. I want to delete columns # 9, 13, and 15.

There would, obviously, then be 12 columns.

I want to freeze panes at the top row, so only row 1 is visible when scrolling down the spreadsheet (looks like you showed me this in your reply above).

This spreadsheet exports with two sheets (the data are on 'Sheet2') and 'Sheet1' is blank. I want 'Sheet1' to be deleted by the macro.

I want all columns to be autofitted (looks like you showed me this in your reply above).

I want the 6th column (after having deleted 9th, 13th, and 15th) to be formatted with 'Center' and bold, red text. I don't need to change the default 'Arial', and '10' text though.

Lastly, at least for the time being, I've discovered that sometimes this report will show a total at the bottom of column 9 (after deleting the 9th, 13th, and 15th columns). This happens because sometimes the entire data is displayed within 30 or so rows. If this isn't the case, there won't be a total. If it is the case, a total will show at the bottom of column 9 (after deleting the 9th, 13th, and 15th columns). This total will be the only data in the bottom most row of the spreadsheet.

However, I want a total regardless of whether the data has one or not. This might make the macro more complicated, but I'm fine and I'm sure you can probably fix this. So I'd want the actual total to be at the bottom of column 9 (after deleting the 9th, 13th, and 15th columns). In the cell to the left of this amount, I want the word 'TOTAL'.

I want the 'TOTAL' text and the actual total amount to be bold and red.

I think I've got most of it covered for a single run of this.

Again, the number of rows is variable. The columns are static - always exports the same number and title for each in the same positions.

Thanks so much, sir.

 

[Alex881]

Also, I'd like to add a column to the right, once done with the above that says 'COMMENTS' and this would be bolded text (the header) and the entire column would be centered.

 

[Alex881]

As another follow up, your code in post #4 is working splendidly. I just am not using the code relating to 'TOTAL' yet, since my problem is that some of them will have a total at the bottom of that particular column, while others won't (as I described in post #5 ). But the freezing of the panes, the auto fitting columns, and the deletion of the correct columns is on point.

I can't wait for the rest of the code though - appreciate all your support.

 

[DanteAmor]

Thanks for your assistance, Dante.

The document exports with 15 columns. I want to delete columns # 9, 13, and 15.

There would, obviously, then be 12 columns.

I want to freeze panes at the top row, so only row 1 is visible when scrolling down the spreadsheet (looks like you showed me this in your reply above).

This spreadsheet exports with two sheets (the data are on 'Sheet2') and 'Sheet1' is blank. I want 'Sheet1' to be deleted by the macro.

I want all columns to be autofitted (looks like you showed me this in your reply above).

I want the 6th column (after having deleted 9th, 13th, and 15th) to be formatted with 'Center' and bold, red text. I don't need to change the default 'Arial', and '10' text though.

Lastly, at least for the time being, I've discovered that sometimes this report will show a total at the bottom of column 9 (after deleting the 9th, 13th, and 15th columns). This happens because sometimes the entire data is displayed within 30 or so rows. If this isn't the case, there won't be a total. If it is the case, a total will show at the bottom of column 9 (after deleting the 9th, 13th, and 15th columns). This total will be the only data in the bottom most row of the spreadsheet.

However, I want a total regardless of whether the data has one or not. This might make the macro more complicated, but I'm fine and I'm sure you can probably fix this. So I'd want the actual total to be at the bottom of column 9 (after deleting the 9th, 13th, and 15th columns). In the cell to the left of this amount, I want the word 'TOTAL'.

I want the 'TOTAL' text and the actual total amount to be bold and red.

I think I've got most of it covered for a single run of this.

Again, the number of rows is variable. The columns are static - always exports the same number and title for each in the same positions.

Thanks so much, sir.

Try this

Sub tryn()
' tryn Macro
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    Cells.EntireColumn.AutoFit          '<-- AUTOFIT all columns
    ActiveWindow.FreezePanes = False
    Range("A2").Select
    ActiveWindow.FreezePanes = True     '<--Freeze row 1
    
    Columns(15).EntireColumn.Delete Shift:=xlToLeft    '<-- Write the letter of the column you want to delete
    Columns(13).EntireColumn.Delete Shift:=xlToLeft    '<-- Write the letter of the column you want to delete
    Columns(9).EntireColumn.Delete Shift:=xlToLeft    '<-- Write the letter of the column you want to delete
    
    Sheets(1).Delete
    
    With Columns(6).Font
        .Bold = True
        .Color = -16776961
        .TintAndShade = 0
        .Name = "Arial"
        .Size = 10
    End With
    
    lr = Cells(Rows.Count, 8).End(xlUp).Row
    Cells(lr + 1, 8).Value = "TOTAL"
    Cells(lr + 1, 9).Value = WorksheetFunction.Sum(Range(Cells(2, 9), Cells(lr, 9)))
    
    With Range(Cells(lr + 1, 8), Cells(lr + 1, 9)).Font
        .Bold = True
        .Color = -16776961
        .TintAndShade = 0
        .Name = "Arial"
        .Size = 10
    End With
    
    MsgBox "End"
End Sub

 

[Alex881]

Thanks for the reply, Dante.

A bit confused - I try to run it (been deleting and renaming my Macros), but don't get much done - it errors out. I click 'Step Into' and this is the first thing I see:

Sub HELLO() <--THERE'S A YELLOW ARROW COMING FROM THE LEFT OF THE SCREEN SHOWING ON THIS LINE.
' HELLO Macro



Also, can I include the notes you put inside the macro (e.g., '<-- Write the letter of the column you want to delete)?

I know I must be doing something wrong...I'm new to this.

 

[DanteAmor]

Delete your macros
Put your original sheets and run my macro, do not change anything.


If it sends you an error tell me the error text.