Variable rows in macro

M

Mhtacker

New Member
Joined
Nov 18, 2023
Messages
13
Platform
  1. Windows
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?
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Sort by date Sort by votes
VBA Code: 
Sub insertrows()
'
' insertrows Macro
'
' Keyboard Shortcut: Ctrl+x
'
    Range("A16").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range("A16:L60").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:=Range("A17:A60" _
        ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sorted").Sort
        .SetRange Range("A16:L60")
        .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
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    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
 
Last edited by a moderator:
Upvote 0
So, you are trying to find the last row? The following sets the last row number as lRow.
VBA Code: 
Dim wb as Workbook, sht as Worksheet, rng as Range, lRow as Integer
Set wb = ThisWorkbook: Set sht = wb.Sheets("YourSheetNameHere")
lRow = sht.Rows.End(xlDown).Row

The Range could be set by
VBA Code: 
Set rng = sht.Range("A16:A" & lRow)
 
Upvote 0
Skyybot said:
So, you are trying to find the last row? The following sets the last row number as lRow.
VBA Code: 
Dim wb as Workbook, sht as Worksheet, rng as Range, lRow as Integer
Set wb = ThisWorkbook: Set sht = wb.Sheets("YourSheetNameHere")
lRow = sht.Rows.End(xlDown).Row

The Range could be set by
VBA Code: 
Set rng = sht.Range("A16:A" & lRow)
Click to expand...
Yes, I am trying to find the last row when the rows of new data is input to the wksh. I have typo earlier, my range should have read A16.L60 . I’m really “green” in doing this so can you tell me exactly where I would type the code(s) you show and do I input both (IRow and Range)? Thanks for your help,
 
Upvote 0
Assuming you run Macro on ActiveSheet of your Workbook:
VBA Code: 
Sub insertrows()
'
' insertrows Macro
'
' Keyboard Shortcut: Ctrl+x
'
    Dim wb As Workbook, sht As Worksheet, rng As Range, lRow As Integer
    Set wb = ThisWorkbook: Set sht = wb.ActiveSheet
    lRow = sht.Rows.End(xlDown).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:=Range(rng _
        ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sorted").Sort
        .SetRange Range("A16:L60")
        .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
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    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
 
Upvote 0
Skyybot said:
Assuming you run Macro on ActiveSheet of your Workbook:
VBA Code: 
Sub insertrows()
'
' insertrows Macro
'
' Keyboard Shortcut: Ctrl+x
'
    Dim wb As Workbook, sht As Worksheet, rng As Range, lRow As Integer
    Set wb = ThisWorkbook: Set sht = wb.ActiveSheet
    lRow = sht.Rows.End(xlDown).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:=Range(rng _
        ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sorted").Sort
        .SetRange Range("A16:L60")
        .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
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    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
Click to expand...
Skyybot said:
Assuming you run Macro on ActiveSheet of your Workbook:
VBA Code: 
Sub insertrows()
'
' insertrows Macro
'
' Keyboard Shortcut: Ctrl+x
'
    Dim wb As Workbook, sht As Worksheet, rng As Range, lRow As Integer
    Set wb = ThisWorkbook: Set sht = wb.ActiveSheet
    lRow = sht.Rows.End(xlDown).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:=Range(rng _
        ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sorted").Sort
        .SetRange Range("A16:L60")
        .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
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    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
Click to expand...
Thank you so much I will copy this and try it. Do I write the same as you when my macro switches to another worksheet in the file that also has ranges?
 
Upvote 0
You should be okay as long as you are still in the same Workbook. The Range will always be from Column A Row 16 to Column L Row (last).
 
Upvote 0
Skyybot said:
You should be okay as long as you are still in the same Workbook. The Range will always be from Column A Row 16 to Column L Row (last).
Click to expand...
Hi there, it’s me again. I copied the above into my file over my initial macro data and ran it. It ran but still only picked up 60 rows as though your added programming did not execute. I’m sure I’ve done something incorrectly. Any suggestions?
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
Help deleting rows and creating range name via VBA
Replies
2
Views
115
EssKayKay
E
M
  • Question
Need help with a macro
Replies
3
Views
99
Michael M
Michael M
M
  • Question
Creating a model - Unable to get trend function to run properly
Replies
2
Views
184
Misono
M
A
  • Solved
Comparing two excel files for matching / mismatching rows (long text strings - match() wont work
Replies
4
Views
560
ahaye
A
I
  • Question
Macro issue
Replies
2
Views
336
Imajica
I

Forum statistics

Threads
1,215,068
Messages
6,122,950
Members
449,095
Latest member
nmaske

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top