Variable rows in macro

[Mhtacker]

I created a macro to insert new row after change in column A. It works great. However I didn’t realize that the number of rows in the original data could vary. It may be more rows or less rows than what I initially had when creating the macro. I’m not good at coding so unable to understand how to make the macro recognize change in number of rows. Can you help?

 

[MARK858]

Try changing the line in red to

.SetRange rng

  With ActiveWorkbook.Worksheets("Sorted").Sort
        .SetRange Range("A16:L60")

 

[MARK858]

Also change

lRow As Integer

to

lRow As Long

and

ActiveWorkbook.Worksheets("Sorted").Sort.SortFields.Add2 Key:=Range(rng _
        ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

to

    ActiveWorkbook.Worksheets("Sorted").Sort.SortFields.Add2 Key:=Range("A16") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
l

 

[Mhtacker]

Also change

lRow As Integer

to

lRow As Long

and

ActiveWorkbook.Worksheets("Sorted").Sort.SortFields.Add2 Key:=Range(rng _
        ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

to

    ActiveWorkbook.Worksheets("Sorted").Sort.SortFields.Add2 Key:=Range("A16") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
l

thank you I will try this

 

[MARK858]

Make sure that you make the change in the previous post to the one that you quoted (post 11)

 

[Mhtacker]

Also change

lRow As Integer

to

lRow As Long

and

ActiveWorkbook.Worksheets("Sorted").Sort.SortFields.Add2 Key:=Range(rng _
        ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

to

    ActiveWorkbook.Worksheets("Sorted").Sort.SortFields.Add2 Key:=Range("A16") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
l

I am getting a "select method of range class failed" in excuting the macro with the changes.

 

[MARK858]

On what line (especially as none of the changes have a .Select)?

 

[MARK858]

This line

ActiveWorkbook.Worksheets("Sorted").Sort.SortFields.Add2 Key:=Range(rng _
        ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

change to

    ActiveWorkbook.Worksheets("Sorted").Sort.SortFields.Add2 Key:=Worksheets("Sorted").Range("A16) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

I don't think it is causing the error that you quoted so still need to know what line is highlighted, but still needs changing from what I previously stated.

 

[Mhtacker]

This line

ActiveWorkbook.Worksheets("Sorted").Sort.SortFields.Add2 Key:=Range(rng _
        ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

change to

    ActiveWorkbook.Worksheets("Sorted").Sort.SortFields.Add2 Key:=Worksheets("Sorted").Range("A16) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

I don't think it is causing the error that you quoted so still need to know what line is highlighted, but still needs changing from what I previously stated.

Its the 5th line down of original code that states rng.select - sorry, im really not good at this so don't know exactly what im doing. I did make all the changes you posted.

 

[MARK858]

What happens when you run the code below (btw don't quote posts unless they are relevant, just reply)

Sub insertrows()
'
' insertrows Macro
'
' Keyboard Shortcut: Ctrl+x
'
    Dim wb As Workbook, sht As Worksheet, rng As Range, lRow As Long
 
    Set wb = ThisWorkbook: Set sht = wb.ActiveSheet
    lRow = sht.Cells(Rows.Count, "A").End(xlUp).Row
 
    Set rng = sht.Range("A16:L" & lRow)
    rng.Select
 
    Selection.Copy
    Sheets.Add After:=ActiveSheet
    Sheets("Sorted").Select
 
    Range("A16").Select
    ActiveSheet.Paste
 
    Application.CutCopyMode = False
    ActiveWorkbook.Worksheets("Sorted").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sorted").Sort.SortFields.Add2 Key:=Worksheets("Sorted").Range("A16") _
    , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
 
    With ActiveWorkbook.Worksheets("Sorted").Sort
        .SetRange rng
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(12), _
        Replace:=True, PageBreaks:=False, SummaryBelowData:=True
    ActiveSheet.Outline.ShowLevels RowLevels:=2
    Range("L18:M18").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range("M18:M92").Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.FormulaR1C1 = "1"
    ActiveSheet.Outline.ShowLevels RowLevels:=3
    Range("L17:M17").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range("M17:M92").Select
    Selection.SpecialCells(xlCellTypeConstants, 1).Select
    Range("M17").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("L17:M17").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range("M17:M92").Select
    Selection.SpecialCells(xlCellTypeConstants, 1).Select
    Selection.EntireRow.Insert
    Columns("M:M").Select
    Range("M5").Activate
    Selection.ClearContents
    ActiveSheet.Outline.ShowLevels RowLevels:=2
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    Range("A17:L123").Select
    ActiveSheet.Outline.ShowLevels RowLevels:=3
    Selection.Copy
    Sheets("Invoice").Select
    Range("A17").Select
    ActiveSheet.Paste
End Sub

 

[Mhtacker]

What happens when you run the code below (btw don't quote posts unless they are relevant, just reply)

Sub insertrows()
'
' insertrows Macro
'
' Keyboard Shortcut: Ctrl+x
'
    Dim wb As Workbook, sht As Worksheet, rng As Range, lRow As Long
 
    Set wb = ThisWorkbook: Set sht = wb.ActiveSheet
    lRow = sht.Cells(Rows.Count, "A").End(xlUp).Row
 
    Set rng = sht.Range("A16:L" & lRow)
    rng.Select
 
    Selection.Copy
    Sheets.Add After:=ActiveSheet
    Sheets("Sorted").Select
 
    Range("A16").Select
    ActiveSheet.Paste
 
    Application.CutCopyMode = False
    ActiveWorkbook.Worksheets("Sorted").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sorted").Sort.SortFields.Add2 Key:=Worksheets("Sorted").Range("A16") _
    , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
 
    With ActiveWorkbook.Worksheets("Sorted").Sort
        .SetRange rng
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(12), _
        Replace:=True, PageBreaks:=False, SummaryBelowData:=True
    ActiveSheet.Outline.ShowLevels RowLevels:=2
    Range("L18:M18").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range("M18:M92").Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.FormulaR1C1 = "1"
    ActiveSheet.Outline.ShowLevels RowLevels:=3
    Range("L17:M17").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range("M17:M92").Select
    Selection.SpecialCells(xlCellTypeConstants, 1).Select
    Range("M17").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("L17:M17").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range("M17:M92").Select
    Selection.SpecialCells(xlCellTypeConstants, 1).Select
    Selection.EntireRow.Insert
    Columns("M:M").Select
    Range("M5").Activate
    Selection.ClearContents
    ActiveSheet.Outline.ShowLevels RowLevels:=2
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    Range("A17:L123").Select
    ActiveSheet.Outline.ShowLevels RowLevels:=3
    Selection.Copy
    Sheets("Invoice").Select
    Range("A17").Select
    ActiveSheet.Paste
End Sub

The macro takes the first sheet in the work book and copies to second wksh where it sorts by column A, subtotals the sheet, then adds a row to each change in Column A then the sheet is copied to the last wksh for final output.