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

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
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

J
  • Question Question
VBA removing heading in spreadsheet
Replies
3
Views
263
rlv01
R
C
  • Solved
Please help me clean up my VBA script, I'm having trouble with redundancies
Replies
4
Views
441
CapRavOr
C
S
  • Question Question
Macro Formatting to all WS
Replies
4
Views
315
MARK858
MARK858
C
  • Question Question
How to automatically paste the data from one sheet to another using VBA
Replies
4
Views
235
Alex Blakenburg
A
D
  • Solved
VBA If statement to continue
2
Replies
17
Views
604
dinunan
D

Forum statistics

Threads
1,223,896
Messages
6,175,262
Members
452,627
Latest member
KitkatToby

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