Urgent - Macro for different number of rows

E

excelisgreat07

New Member
Joined
Jul 10, 2022
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
I have made a macro, which works as it should. The problem arises when the number of rows either increase or decrease. It runs the formulae for the same number of rows as they were while the macro was being made. I want it to adapt to the number of rows. Example: I built the macro on a sheet having 100 rows. Now my other sheet for other month has 150 customer orders (150 rows). How to make the macro cover all the 150 rows rather than just covering 100 rows?
PS: Columns are fixed in all the sheets. Only rows increase or decrease.
Please help me at the earliest. Urgent.
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Sort by date Sort by votes
Hi & welcome to the forum,

You may refer to the below example
VBA Code: 
Range("A1:A100") 'Fixed 

Range("A1", Range("A" & Rows.Count).End(xlUp))
 
Upvote 0
shall i send you my VB code? Please tell me the changes in that. It will be of great help! I recorded macro using the inbuilt excel feature. Please tell me what to replace in the VB Code with what.
 
Upvote 0
You can provide your existing code & I see if I could make necessary changes
 
Upvote 0
VBA Code: 
Columns("H:H").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("G:G").Select
    Selection.TextToColumns Destination:=Range("G1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 3), Array(10, 1)), TrailingMinusNumbers:=True
    Range("G1").Select
    ActiveCell.FormulaR1C1 = "Order Date"
    Range("H1").Select
    ActiveCell.FormulaR1C1 = "Time"
    Columns("G:G").Select
    Selection.NumberFormat = "m/d/yyyy"
    Columns("H:H").Select
    Selection.NumberFormat = "[$-x-systime]h:mm:ss AM/PM"
    Columns("E:E").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("E:E").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "Check 1"
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "Check 2"
    Range("G1").Select
    ActiveCell.FormulaR1C1 = "Index"
    Range("E2").Select
    ActiveCell.FormulaR1C1 = "=R[1]C[-1]=RC[-1]"
    Range("D2").Select
    Selection.End(xlDown).Select
    Range("E11638").Select
    Range(Selection, Selection.End(xlUp)).Select
    Selection.FillDown
    Selection.End(xlUp).Select
    Range("F2").Select
    ActiveCell.FormulaR1C1 = "=RC[43]=RC[48]"
    Range("D3").Select
    Selection.End(xlDown).Select
    Range("F11638").Select
    Range(Selection, Selection.End(xlUp)).Select
    Selection.FillDown
    Selection.End(xlUp).Select
    Selection.AutoFilter
    Range("F1").Select
    ActiveSheet.Range("$A$1:$DP$11638").AutoFilter Field:=6, Criteria1:="TRUE"
    ActiveSheet.Range("$A$1:$DP$11638").AutoFilter Field:=5, Criteria1:="TRUE"
    Range("G16").Select
    ActiveCell.FormulaR1C1 = "1"
    Range("D16").Select
    Selection.End(xlDown).Select
    Range("G11621").Select
    Range(Selection, Selection.End(xlUp)).Select
    Selection.FillDown
    Selection.End(xlUp).Select
    Range("G1").Select
    ActiveSheet.Range("$A$1:$DP$11638").AutoFilter Field:=5
    ActiveSheet.Range("$A$1:$DP$11638").AutoFilter Field:=6
    ActiveWorkbook.Worksheets("Present").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Present").AutoFilter.Sort.SortFields.Add Key:= _
        Range("G1:G11638"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
        :=xlSortNormal
    With ActiveWorkbook.Worksheets("Present").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveSheet.Range("$A:$DP").AutoFilter Field:=7, Criteria1:="<>"
    Rows("2:2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete Shift:=xlUp
    ActiveSheet.Range("$A:$DP").AutoFilter Field:=7
    Columns("G:G").Select
    Selection.Delete Shift:=xlToLeft
    Range("DP1").Select
    ActiveCell.FormulaR1C1 = "Refund"
    Range("DP2").Select
    ActiveCell.FormulaR1C1 = "N"
    Range("DM2").Select
    Selection.End(xlDown).Select
    Range("DP").Select
    Range(Selection, Selection.End(xlUp)).Select
    Selection.FillDown
    Selection.End(xlUp).Select
    Range("DQ1").Select
    ActiveCell.FormulaR1C1 = "Subcategory"
    Range("DQ2").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(""*""&C[-84]&""*"",'Subcategory Match'!R1C1:R5018C2,2,0)"
    Range("DQ2").Select
    Selection.AutoFill Destination:=Range("DQ2:DQ7"), Type:=xlFillDefault
    Range("DQ2:DQ7").Select
    Range("DP2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range("DP2").Select
    Selection.End(xlDown).Select
    Range("DQ9963").Select
    Range(Selection, Selection.End(xlUp)).Select
    Selection.FillDown
    Selection.End(xlUp).Select
    Range("DR1").Select
    ActiveCell.FormulaR1C1 = "Segment"
    Range("DR2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(C[-119]=""Employee Website"",""B2E"",IF(C[-119]=""Main Website"",B2C,""B2B""))"
    Range("DQ2").Select
    Selection.End(xlDown).Select
    Range("DR").Select
    Range(Selection, Selection.End(xlUp)).Select
    Selection.FillDown
    Range("DR2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(C[-119]=""Employee Website"",""B2E"",IF(C[-119]=""Main Website"",""B2C"",""B2B""))"
    Range("DR3").Select
    ActiveWindow.SmallScroll Down:=-6
    Range("DR2").Select
    Selection.AutoFill Destination:=Range("DR2:DR12"), Type:=xlFillDefault
    Range("DR2:DR12").Select
    Range("DQ2").Select
    Selection.End(xlDown).Select
    Range("DR9963").Select
    Range(Selection, Selection.End(xlUp)).Select
    Range("DQ9820").Select
    Selection.End(xlUp).Select
    Range("DQ3").Select
    Selection.End(xlDown).Select
    Range("DR9963").Select
    Range(Selection, Selection.End(xlUp)).Select
    Range("DR3:DR9963").Select
    Range("DR9963").Activate
    Selection.FillDown
    Range("DR9961").Select
    Selection.End(xlUp).Select
    Range("DS1").Select
    ActiveCell.FormulaR1C1 = "Zone"
    Range("DS2").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(""*""&C[-94]&""*"",'Region Match'!R1C1:R40C2,2,0)"
    Range("DS2:DS9").Select
    Selection.FillDown
    Range("DR2").Select
    Selection.End(xlDown).Select
    Range("DS9963").Select
    Range(Selection, Selection.End(xlUp)).Select
    Selection.FillDown
    ActiveWindow.ScrollRow = 1
    Range("DT1").Select
    ActiveCell.FormulaR1C1 = "BAU/ Online"
    Range("DT2").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(""*""&C[-87]&""*"",'BAU Online Match'!R1C1:R720C5,3,0)"
    Range("DT2:DT8").Select
    Selection.FillDown
    Range("DS2").Select
    Selection.End(xlDown).Select
    Range("DT9963").Select
    Range(Selection, Selection.End(xlUp)).Select
    Selection.FillDown
    Range("DM18").Select
    ActiveWindow.ScrollColumn = 114
    ActiveWindow.ScrollColumn = 113
    ActiveWindow.ScrollColumn = 112
    ActiveWindow.ScrollColumn = 111
    ActiveWindow.ScrollColumn = 110
    ActiveWindow.ScrollColumn = 109
    ActiveWindow.ScrollColumn = 108
    ActiveWindow.ScrollColumn = 106
    ActiveWindow.ScrollColumn = 105
    ActiveWindow.ScrollColumn = 104
    ActiveWindow.ScrollColumn = 103
    ActiveWindow.ScrollColumn = 102
    ActiveWindow.ScrollColumn = 100
    ActiveWindow.ScrollColumn = 99
    ActiveWindow.ScrollColumn = 98
    ActiveWindow.ScrollColumn = 97
    ActiveWindow.ScrollColumn = 95
    ActiveWindow.ScrollColumn = 88
    ActiveWindow.ScrollColumn = 86
    ActiveWindow.ScrollColumn = 84
    ActiveWindow.ScrollColumn = 81
    ActiveWindow.ScrollColumn = 79
    ActiveWindow.ScrollColumn = 77
    ActiveWindow.ScrollColumn = 75
    ActiveWindow.ScrollColumn = 73
    ActiveWindow.ScrollColumn = 72
    ActiveWindow.ScrollColumn = 71
    ActiveWindow.ScrollColumn = 68
    ActiveWindow.ScrollColumn = 67
    ActiveWindow.ScrollColumn = 65
    ActiveWindow.ScrollColumn = 63
    ActiveWindow.ScrollColumn = 61
    ActiveWindow.ScrollColumn = 59
    ActiveWindow.ScrollColumn = 57
    ActiveWindow.ScrollColumn = 52
    ActiveWindow.ScrollColumn = 50
    ActiveWindow.ScrollColumn = 48
    ActiveWindow.ScrollColumn = 45
    ActiveWindow.ScrollColumn = 43
    ActiveWindow.ScrollColumn = 40
    ActiveWindow.ScrollColumn = 38
    ActiveWindow.ScrollColumn = 35
    ActiveWindow.ScrollColumn = 33
    ActiveWindow.ScrollColumn = 30
    ActiveWindow.ScrollColumn = 28
    ActiveWindow.ScrollColumn = 26
    ActiveWindow.ScrollColumn = 24
    ActiveWindow.ScrollColumn = 23
    ActiveWindow.ScrollColumn = 20
    ActiveWindow.ScrollColumn = 19
    ActiveWindow.ScrollColumn = 18
    ActiveWindow.ScrollColumn = 17
    ActiveWindow.ScrollColumn = 16
    ActiveWindow.ScrollColumn = 15
    ActiveWindow.ScrollColumn = 14
    ActiveWindow.ScrollColumn = 13
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 1
    ActiveWindow.ScrollRow = 1
End Sub
 
Last edited by a moderator:
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Ladder League Workbook, replace one row with another
Replies
2
Views
230
CordingBags
C
A
  • Solved
Comparing two excel files for matching / mismatching rows (long text strings - match() wont work
Replies
4
Views
539
ahaye
A
C
  • Question
Insert row, copy formulas, insert and rename sheet with macro or script
Replies
0
Views
365
Colonel
C
A
  • Question
VBA Copy data from 1 Excel file to multiple Excel Files.
Replies
0
Views
397
aurelius142
A
F
  • Question
Finding numbers between values
Replies
1
Views
338
AlanY
A

Forum statistics

Threads
1,214,649
Messages
6,120,732
Members
448,987
Latest member
marion_davis

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