Dynamic Data for Macro - Stop Calculating

F

FanofExcel18

Board Regular
Joined
Jun 7, 2018
Messages
65
I have dynamic report that will grow by about 200 rows per day. I'm new to VBA and trying to get the formulas to only calculate based on Active or filled rows.

Any help would be great:
Code: 
Sub step1()
'
' step1 Macro
'
' Keyboard Shortcut: Ctrl+r
'
    Columns("W:W").Select
    Selection.Delete Shift:=xlToLeft
    Range("W2").Select
    ActiveCell.FormulaR1C1 = "=TEXT(RC[-3],""hh AM/PM"")"
    Range("X2").Select
    ActiveCell.FormulaR1C1 = "=TEXT(RC[-4],""mm/dd/yyyy"")"
    Range("Y2").Select
    ActiveCell.FormulaR1C1 = "=TEXT(RC[-3],""mm/dd/yyyy"")"
    Range("Z2").Select
    ActiveCell.FormulaR1C1 = "=MONTH(RC[-6])"
    Range("AA2").Select
    ActiveCell.FormulaR1C1 = "=TEXT(RC[-7],""ddd"")"
    Range("AB2").Select
    ActiveCell.FormulaR1C1 = "=RC[-8]"
    Range("AC2").Select
    ActiveCell.FormulaR1C1 = "=(RC[-7]-RC[-9])*1440"
    Range("AD2").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]/60"
    Range("AE2").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]/24"
    Range("AF2").Select
    ActiveCell.FormulaR1C1 = "=TODAY()-RC[-11]"
    Range("AG2").Select
        Range("AB2").Select
    ActiveCell.FormulaR1C1 = _
        "=WEEKNUM(RC[-8],1)-WEEKNUM(DATE(YEAR(RC[-8]),MONTH(RC[-8]),1),1)+1"
    Range("AG2").Select
        Columns("AB:AB").Select
    Selection.NumberFormat = "General"
    Columns("AF:AF").Select
    Selection.NumberFormat = "General"
    Range("AG2").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-29],Sheet2!C[-26]:C[-25],2,0)"
    Range("AH2").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-26],Sheet2!C[-33]:C[-31],3,0)"
    Range("AI2").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(Sheet1!RC[-27],Sheet2!C[-34]:C[-33],2,0)"
    Range("W2:AI2").Select
    Selection.AutoFill Destination:=Range("W2:AI17578")
    Range("W2:AI17578").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("AC2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.NumberFormat = "0"
    Range("AF2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
  Selection.NumberFormat = "0"
    Range("AD4:AE4").Select
    Range(Selection, Selection.End(xlDown)).Select
  Selection.NumberFormat = "0"
    Range("AC2:AE2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Replace What:="#VALUE!", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Range("AC17578").Select
       Columns("AH:AI").Select
    Range("AI17571").Activate
    Selection.Replace What:="#N/A", Replacement:="N/A", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Columns("W:W").Select
    Range("W17571").Activate
    Selection.TextToColumns Destination:=Range("W1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
    Range("A1").Select
End Sub
 
Last edited by a moderator:

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Sort by date Sort by votes
Welcome to the Board!

You can dynamically find the last row with data by picking a column that will always have data in it. Let's say it is column A.
Then we can find the last row like this:
Code: 
Dim lr as Long
lr = Cells(Rows.Count,"A").End(xlUp).Row
Then, just replace the last row in all your formulas with this variables (you don't need to use Autofill, you can assign the whole column of formulas at once). i.e.
Code: 
Range("W2:W" & lr).FormulaR1C1 = "=TEXT(RC[-3],""hh AM/PM"")"
Range("X2:X" & lr).FormulaR1C1 = "=TEXT(RC[-4],""mm/dd/yyyy"")"
...
 
Upvote 0
Joe4 said:
Welcome to the Board!

You can dynamically find the last row with data by picking a column that will always have data in it. Let's say it is column A.
Then we can find the last row like this:
Code: 
Dim lr as Long
lr = Cells(Rows.Count,"A").End(xlUp).Row
Then, just replace the last row in all your formulas with this variables (you don't need to use Autofill, you can assign the whole column of formulas at once). i.e.
Code: 
Range("W2:W" & lr).FormulaR1C1 = "=TEXT(RC[-3],""hh AM/PM"")"
Range("X2:X" & lr).FormulaR1C1 = "=TEXT(RC[-4],""mm/dd/yyyy"")"
...
Click to expand...

Joe4,

how can I make this section dynamic? I've attempted several searches online and its not working:
Selection.AutoFill Destination:=Range("W2:AI17578")
Range("W2:AI17578").Select

I've attempted:


Selection.AutoFill Destination:=Range("W2:AI2", & lr)
Range("W2:AI2", & lr).Select

as well as this:
& Range("A" & Rows.Count).End(xlUp).Row)

</pre>
 
Upvote 0
Range("W2:AI2", & lr).
Click to expand...
Why did you leave the "2" in there after the "AI" column number?
Remember, lr is our calculated last row number. It replaces the ending row number ("2"), i.e.
Code: 
[COLOR=#333333]Range("W2:AI" & lr).[/COLOR]
 
Last edited:
Upvote 0
Joe4 said:
Why did you leave the "2" in there after the "AI" column number?
Remember, lr is our calculated last row number. It replaces the ending row number ("2"), i.e.
Code: 
[COLOR=#333333]Range("W2:AI" & lr).[/COLOR]
Click to expand...

Run-time error '1004':
Method 'Range' of object'_Global' failed

Selection.AutoFill Destination:=Range("W2:AI" & lr)
 
Upvote 0
You don't need the autofill part anymore, my earlier code replaces it.

You were first doing this two step process:
- populate row 2 for each column individually
- autofill row 2 down to the last row for all columns

The method I presented eliminates the need for the autofill step since you are populating all the cells you need in each column at once.

Also, just about every line that sends in "Select" and the next line starts with "Selection" or "ActiveCell" can be combined.
The Macro Recorder is very literal and records each of the range selections. But it is not necessary to actually select the range in VBA to work with it.
Not only does combining those rows make your code shorter, but it makes it faster too.

So, assuming that you can use column A to dynamically determine where the last row of data on your sheet is, you can replace this whole section:
Code: 
    Range("W2").Select
    ActiveCell.FormulaR1C1 = "=TEXT(RC[-3],""hh AM/PM"")"
    Range("X2").Select
    ActiveCell.FormulaR1C1 = "=TEXT(RC[-4],""mm/dd/yyyy"")"
    Range("Y2").Select
    ActiveCell.FormulaR1C1 = "=TEXT(RC[-3],""mm/dd/yyyy"")"
    Range("Z2").Select
    ActiveCell.FormulaR1C1 = "=MONTH(RC[-6])"
    Range("AA2").Select
    ActiveCell.FormulaR1C1 = "=TEXT(RC[-7],""ddd"")"
    Range("AB2").Select
    ActiveCell.FormulaR1C1 = "=RC[-8]"
    Range("AC2").Select
    ActiveCell.FormulaR1C1 = "=(RC[-7]-RC[-9])*1440"
    Range("AD2").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]/60"
    Range("AE2").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]/24"
    Range("AF2").Select
    ActiveCell.FormulaR1C1 = "=TODAY()-RC[-11]"
    Range("AG2").Select
        Range("AB2").Select
    ActiveCell.FormulaR1C1 = _
        "=WEEKNUM(RC[-8],1)-WEEKNUM(DATE(YEAR(RC[-8]),MONTH(RC[-8]),1),1)+1"
    Range("AG2").Select
        Columns("AB:AB").Select
    Selection.NumberFormat = "General"
    Columns("AF:AF").Select
    Selection.NumberFormat = "General"
    Range("AG2").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-29],Sheet2!C[-26]:C[-25],2,0)"
    Range("AH2").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-26],Sheet2!C[-33]:C[-31],3,0)"
    Range("AI2").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(Sheet1!RC[-27],Sheet2!C[-34]:C[-33],2,0)"
    Range("W2:AI2").Select
    Selection.[COLOR=#ff0000]AutoFill[/COLOR] Destination:=Range("W2:AI17578")
with this:
Code: 
    Dim lr As Long
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    
    Range("W2:W" & lr).FormulaR1C1 = "=TEXT(RC[-3],""hh AM/PM"")"
    Range("X2:X" & lr).FormulaR1C1 = "=TEXT(RC[-4],""mm/dd/yyyy"")"
    Range("Y2:Y" & lr).FormulaR1C1 = "=TEXT(RC[-3],""mm/dd/yyyy"")"
    Range("Z2:Z" & lr).FormulaR1C1 = "=MONTH(RC[-6])"
    Range("AA2:AA" & lr).FormulaR1C1 = "=TEXT(RC[-7],""ddd"")"
    Range("AB2:AB" & lr).FormulaR1C1 = "=RC[-8]"
    Range("AC2:AC" & lr).FormulaR1C1 = "=(RC[-7]-RC[-9])*1440"
    Range("AD2:AD" & lr).FormulaR1C1 = "=RC[-1]/60"
    Range("AE2:AE" & lr).FormulaR1C1 = "=RC[-1]/24"
    Range("AF2:AF" & lr).FormulaR1C1 = "=TODAY()-RC[-11]"
    Range("AG2:AG" & lr).FormulaR1C1 = "=VLOOKUP(RC[-29],Sheet2!C[-26]:C[-25],2,0)"
    Range("AH2:AH" & lr).FormulaR1C1 = "=VLOOKUP(RC[-26],Sheet2!C[-33]:C[-31],3,0)"
    Range("AI2:AI" & lr).FormulaR1C1 = "=VLOOKUP(Sheet1!RC[-27],Sheet2!C[-34]:C[-33],2,0)"
 
Upvote 0
You are welcome.
Glad I was able to help!:)
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Insert formula in last column macro
2
Replies
13
Views
215
MARK858
MARK858
Dazzawm
  • Solved
Change Range To Recorded Macro
Replies
3
Views
191
Alex Blakenburg
A
S
  • Solved
VBA autofilling code
Replies
6
Views
603
Peter_SSs
Peter_SSs
P
  • Question Question
Help with Vba Code
Replies
3
Views
478
Coyotex3
C
L
  • Solved
VBA remove leading zeros
2
Replies
14
Views
2K
Joe4
Joe4

Forum statistics

Threads
1,224,818
Messages
6,181,152
Members
453,021
Latest member
Justyna P

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