correct macro autofill code for dynamic ranges

R

reader6886

New Member
Joined
Sep 10, 2020
Messages
25
Office Version
  1. 2019
Platform
  1. Windows
Hello all beginner here.

How do I change the following vb codes so that the macros can be used for sheets with dynamic ranges.

1 Dynamic autofill ( even though autofill was done with autofill dot ) macro reording produced the code with absolute range(G2:G799). How do i make it dynamic.So autofill on other file dont miss any cells or overshoot !

VBA Code: 
 Range("G2").Select
    ActiveCell.FormulaR1C1 = "=LEFT(RC[-2],65)"
    Range("G2").Select
    Selection.AutoFill Destination:=Range("G2:G799")
    Range("G2:G799").Select


2.how do I edit the table range to make it dynamic . Even though using the ctrl+**** + right/ left arrow produced the absolute range($A$1:$F$799) like below.
VBA Code: 
Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$F$799"), , xlYes).name = _
        "Table1"
    Range("Table1[#All]").Select

3. what do I use instead of the following code/range(change G2:G1007) so that when used with files with more or less row/columns the formula does not produce unnecessary characters click here in rows with no hyperlinks .
VBA Code: 
Range("G2").Select
    ActiveCell.FormulaR1C1 = "=HYPERLINK(RC[-3],""click here"")"
    Range("G2").Select
    Selection.AutoFill Destination:=Range("G2:G1007")
    Range("G2:G1007").Select


thanks in advance for tips.
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Sort by date Sort by votes
For 1 you can use
VBA Code: 
   With Range("G2")
      .FormulaR1C1 = "=LEFT(RC[-2],65)"
      .AutoFill Destination:=Range("G2:G" & Range("E" & Rows.Count).End(xlUp).Row)
    End With
The same for 3 but use column D and for 2 use
VBA Code: 
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("A1").CurrentRegion, , xlYes).Name = "Table1"
 
Upvote 1
Hi reader6886

The clever way of doing this is to create a dynamic table (table created by Home tab-->Styles-->Format As Table.
You can use a macro to put a table around the range

VBA Code: 
Sub DynamicTables()


    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.Calculation = xlManual

    Dim sht As Worksheet
    Dim rng As Range
    Dim StartCell As Range
    Dim objTable As ListObject
    
    Worksheets("Sheet1").Activate
    ActiveSheet.Range("1:1").Font.Color = vbWhite
    Set sht = Worksheets("Sheet1")
    Set StartCell = Range("A1")
    'Select Range
    StartCell.CurrentRegion.Select
    Set objTable = ActiveSheet.ListObjects.Add(xlSrcRange, Selection, , xlYes)
    objTable.TableStyle = "TableStyleLight8"
        With ActiveSheet
        .ListObjects(1).Name = "MyTable"
        .Columns.AutoFit
    End With
    
    Set rng = Range("MyTable")
    Range("MyTable").Select
    
    With rng.Borders
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    
    Rows("2:2").EntireRow.Select
    ActiveWindow.FreezePanes = True
    Columns.AutoFit

    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.Calculation = xlAutomatic

End Sub

Then you simply refer to the header to insert the formula into every cell in a column.
E.g.,

VBA Code: 
Worksheets("Sheet1").Activate
Range("MyTable[MyHeader]").Formula = "=LEFT(RC[-2],65)"

This way, you don't have to worry about filling down.

If you haven't already discovered named ranges, I suggest you look it them. You can use a 'text string' named range to refer to either a limited range e.g., Range("A2:A5"), or you can use it to refer to a dynamic range e.g., Range("MyTable[MyHeader]").

To create a named range with VBA:
VBA Code: 
Sub CreateNamedRange()
    Worksheets("Sheet1").Activate
    Range("MyTable[MyHeader]").Select
    ActiveWorkbook.names.Add Name:="NamedRange1", RefersToR1C1:= _
        "=MyTable[MyHeader]"
End Sub

Then you can reference your named range in your VBA:

VBA Code: 
Worksheets("Sheet1").Activate
Range("NamedRange1").Formula = "=LEFT(RC[-2],65)"

Kind regards,

Doug.
 
Upvote 0
dougmarkham said:
Hi reader6886

The clever way of doing this is to create a dynamic table (table created by Home tab-->Styles-->Format As Table.
You can use a macro to put a table around the range

VBA Code: 
Sub DynamicTables()


    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.Calculation = xlManual

    Dim sht As Worksheet
    Dim rng As Range
    Dim StartCell As Range
    Dim objTable As ListObject
   
    Worksheets("Sheet1").Activate
    ActiveSheet.Range("1:1").Font.Color = vbWhite
    Set sht = Worksheets("Sheet1")
    Set StartCell = Range("A1")
    'Select Range
    StartCell.CurrentRegion.Select
    Set objTable = ActiveSheet.ListObjects.Add(xlSrcRange, Selection, , xlYes)
    objTable.TableStyle = "TableStyleLight8"
        With ActiveSheet
        .ListObjects(1).Name = "MyTable"
        .Columns.AutoFit
    End With
   
    Set rng = Range("MyTable")
    Range("MyTable").Select
   
    With rng.Borders
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
   
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
   
    Rows("2:2").EntireRow.Select
    ActiveWindow.FreezePanes = True
    Columns.AutoFit

    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.Calculation = xlAutomatic

End Sub

Then you simply refer to the header to insert the formula into every cell in a column.
E.g.,

VBA Code: 
Worksheets("Sheet1").Activate
Range("MyTable[MyHeader]").Formula = "=LEFT(RC[-2],65)"

This way, you don't have to worry about filling down.

If you haven't already discovered named ranges, I suggest you look it them. You can use a 'text string' named range to refer to either a limited range e.g., Range("A2:A5"), or you can use it to refer to a dynamic range e.g., Range("MyTable[MyHeader]").

To create a named range with VBA:
VBA Code: 
Sub CreateNamedRange()
    Worksheets("Sheet1").Activate
    Range("MyTable[MyHeader]").Select
    ActiveWorkbook.names.Add Name:="NamedRange1", RefersToR1C1:= _
        "=MyTable[MyHeader]"
End Sub

Then you can reference your named range in your VBA:

VBA Code: 
Worksheets("Sheet1").Activate
Range("NamedRange1").Formula = "=LEFT(RC[-2],65)"

Kind regards,

Doug.
Click to expand...
thank you Doug for the knowledge.
 
Upvote 0
Fluff said:
For 1 you can use
VBA Code: 
   With Range("G2")
      .FormulaR1C1 = "=LEFT(RC[-2],65)"
      .AutoFill Destination:=Range("G2:G" & Range("E" & Rows.Count).End(xlUp).Row)
    End With
The same for 3 but use column D and for 2 use
VBA Code: 
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("A1").CurrentRegion, , xlYes).Name = "Table1"
Click to expand...
fluff THANK YOU SO MUCH . very useful and straight to the point. I learnt some good very useful stuff.
 
Upvote 0
Fluff said:
Glad to help & thanks for the feedback.
Click to expand...

I just wanted to ask you about two more similar situation/dynamic range.

1. When I want to delete the duplicates from a column (extending field method).The recorded macro produces the following code . How do I turn it into dynamic one for use in other situations too .How to make $A$1:$E$800 dynamic and not absolute.
VBA Code: 
Columns("D:D").Select
    ActiveSheet.Range("$A$1:$E$800").RemoveDuplicates Columns:=4, Header:=xlYes

2.when sorting a column by A-Z (extended field method) the following code is produced. how do I change the range(A2:E799) into dynamic
VBA Code: 
Columns("E:E").Select
    ActiveWorkbook.Worksheets("best movies ever").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("best movies ever").Sort.SortFields.Add2 Key:=Range _
        ("E1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("best movies ever").Sort
        .SetRange Range("A2:E799")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply

Thank a lot again.
 
Upvote 0
How about
VBA Code: 
Range("A1").CurrentRegion.RemoveDuplicates Columns:=4, Header:=xlYes
and
VBA Code: 
   With ActiveWorkbook.WorkSheets("best movies ever").Range("A1").CurrentRegion
         .Sort .Parent.Range("E1"), xlAscending, , , , , , xlNo
   End With
 
Upvote 0
Range("G2").Select
ActiveCell.FormulaR1C1 = "=HYPERLINK(RC[-3],""click here"")"
Range("G2").Select
Selection.AutoFill Destination:=Range("G2:G1007")
Range("G2:G1007").Select
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question
Help with Vba Code
Replies
3
Views
179
Coyotex3
C
L
  • Solved
VBA remove leading zeros
2
Replies
14
Views
1K
Joe4
Joe4
H
  • Solved
Macro to sort block of data
2
Replies
15
Views
923
Joe4
Joe4
J
  • Question
Autofill Dynamic Selection Range
2
Replies
15
Views
833
Joe4
Joe4
B
  • Question
VBA Selection.Autofil Dynamic Range for columns & rows
Replies
4
Views
734
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,215,477
Messages
6,125,036
Members
449,205
Latest member
Eggy66

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