Change the range of a macro automatically

R

ruzanovsky

New Member
Joined
Jul 12, 2018
Messages
6
Dears,

I want to change the range that the macro pulls automatically, as below:


Code: 
[I]Sub Weekly()
'
' Weekly Macro
'


'
    Range("A2").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    Windows("weekly.csv").Activate
    Columns("A:A").Select
    Selection.Delete Shift:=xlToLeft
    Columns("G:G").Select
    Selection.Cut
    Columns("F:F").Select
    Selection.Insert Shift:=xlToRight
    Selection.Insert Shift:=xlToRight
    Selection.Insert Shift:=xlToRight
    Selection.Insert Shift:=xlToRight
    Columns("E:E").Select
    Selection.TextToColumns Destination:=Range("E1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="/", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1)), _
        TrailingMinusNumbers:=True
    Columns("E:G").Select
    Selection.Delete Shift:=xlToLeft
    Rows("1:1").Select
    Selection.Delete Shift:=xlUp
    Selection.Delete Shift:=xlUp
    Columns("D:D").Select
    Selection.Insert Shift:=xlToRight
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[2]<=100000,1,2)"
    Range("D1").Select
[COLOR=#ff0000][B]    Selection.AutoFill Destination:=Range("D1:D307") ##Here, I want it to select from D1 to last row, not D307[/B][/COLOR]
[COLOR=#ff0000][B]    Range("D1:D307").Select[/B][/COLOR]
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Range("A1").Select
    Selection.End(xlDown).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Delete Shift:=xlUp
[COLOR=#ff0000][B]    Range("A1:H306").Select [B]##Here, I want it to select from A1 to last row, not H306[/B][/B][/COLOR]
[COLOR=#ff0000][B]    Range("A306").Activate[/B][/COLOR]
    Selection.Copy
    Windows("Faturamento_Julho EM10.xlsm").Activate
    Range("A2").Select
    ActiveSheet.Paste
    Sheets("DIN WEEKLY").Select
    Range("C12").Select
    Application.CutCopyMode = False
    ActiveSheet.PivotTables("Tabela dinâmica1").PivotCache.Refresh
    Range("C10:F10").Select
    Selection.Copy
[COLOR=#ff0000][B]    Range("C11:F187").Select ## Here, I want to select from C11 to last row, not F187[/B][/COLOR]
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Range("A8").Select
    Sheets("PLANO DE OV").Select
    Range("A1").Select
End Sub[/I]

How do I apply this?

Regards
 
Last edited by a moderator:

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Sort by date Sort by votes
Thank you for the reply, but I can't make it work..

How do I make the row size dynamic? My column size is static, it does not change.

Range("C11:F187")
 
Upvote 0
After pasting the data, this code from that link should work assuming column F is always there.
LastRow = Range("F" & Rows.Count).End(xlUp).Row

There are multiple ways then to reference. If the starting cell is always the same (C11) and the ending column is always the same then:
Range(Cells(Starting row, starting column),Cells(Ending row, ending column)) -> Range(Cells(11,3),Cells(LastRow,6)
 
Upvote 0
@ruzanovsky, you need to explain how you are defining the dynamic "Last Row" i.e.

Do you mean the last row of a particular column (for instance column A,C or F), if so which?

or

Do you mean the last row of any column?

Does your data has formulas and if yes do you want formulas returning "" to be included in the "Last Row"?
and
Do you want cells which have formats but no data to be included in the "Last Row".
 
Last edited:
Upvote 0
Every column on my table has the same number of rows.
I want the macro to recognize the number of rows that the table has and refresh the ranges.

Sub Weekly()
'
' Weekly Macro
'


'
Range("A2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Windows("weekly.csv").Activate
Columns("A:A").Select
Selection.Delete Shift:=xlToLeft
Columns("G:G").Select
Selection.Cut
Columns("F:F").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("E:E").Select
Selection.TextToColumns Destination:=Range("E1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="/", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1)), _
TrailingMinusNumbers:=True
Columns("E:G").Select
Selection.Delete Shift:=xlToLeft
Rows("1:1").Select
Selection.Delete Shift:=xlUp
Selection.Delete Shift:=xlUp
Columns("D:D").Select
Selection.Insert Shift:=xlToRight
Range("D1").Select
ActiveCell.FormulaR1C1 = "=IF(RC[2]<=100000,1,2)"
Range("D1").Select
Selection.AutoFill Destination:=Range("D1:D307") The macro fills from D1:D307, but my row size changes everyday, so I need it to be D1:"Last Row of the table"
Range("D1:D307").Select Same, I want it to range from D1:"Last row of the table"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("A1").Select
Selection.End(xlDown).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Delete Shift:=xlUp
Range("A1:H306").Select I want it to range from A1:"Last row of the table"
Range("A306").Activate
Selection.Copy
Windows("Faturamento_Julho EM10.xlsm").Activate
Range("A2").Select
ActiveSheet.Paste
Sheets("DIN WEEKLY").Select
Range("C12").Select
Application.CutCopyMode = False
ActiveSheet.PivotTables("Tabela dinâmica1").PivotCache.Refresh
Range("C10:F10").Select
Selection.Copy
Range("C11:F187").Select I want it to range from C11:"Last row of the table"
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("A8").Select
Sheets("PLANO DE OV").Select
Range("A1").Select
End Sub
 
Upvote 0
Ignoring that you haven't answered all the questions maybe....


Code: 
Range("D1").Select
ActiveCell.FormulaR1C1 = "=IF(RC[2]<=100000,1,2)"
Range("D1").Select
Selection.AutoFill Destination:=Range("D1:D307") The macro fills from D1:D307, but my row size changes everyday, so I need it to be D1:"Last Row of the table"
Range("D1:D307").Select Same, I want it to range from D1:"Last row of the table"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

to

Code: 
    With Range("D1:D" & Range("A" & Rows.Count).End(xlUp).Row)
        .FormulaR1C1 = "=IF(RC[2]<=100000,1,2)"
        .Value = .Value
    End With

and

Code: 
Range("A1:H306").Select I want it to range from A1:"Last row of the table"
Range("A306").Activate 
Selection.Copy

to

Code: 
Range("A1:H" & Range("A" & Rows.count).End(xlUp).Row).Copy

and

Code: 
Range("C11:F187").Select I want it to range from C11:"Last row of the table"
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

to

Code: 
Range("C11").PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

and you really don't need all those Selects, Selections and Activates in your code.
 
Upvote 0
We may get our hand slapped for discussing a different question here, but my preference is to add a button. From the developer tab select the button, draw it where you want it, assign macro, the code is literally Run MacroName.
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question
How can i extract the correct leave time on this code?
Replies
1
Views
355
rlv01
R
P
  • Question
Help with Vba Code
Replies
3
Views
146
Coyotex3
C
C
  • Question
How to get my macro to stop at the last recognized line
Replies
6
Views
197
Coco1
C
D
  • Solved
Use the same macro on a weekly file which contains date info
Replies
1
Views
234
John_w
J
R
  • Solved
How to loop a macro
Replies
2
Views
350
rlcurlee
R

Forum statistics

Threads
1,214,606
Messages
6,120,490
Members
448,967
Latest member
visheshkotha

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