Help with autofill

[polska2180]

Hello,

below is a part of the code im using. I want this code to be flexible enough so that any range of data will work with it. Thus I want the autofill part to not be limited to N2:n1860. I may have data thats only N2:n100 or n2:n3000.


Selection.AutoFill Destination:=Range("N2:N1860")
Range("N2:N1860").Select
Range("O2").Select
Selection.AutoFill Destination:=Range("O2:O1860")
Range("O2:O1860").Select
Columns("O:O").Select
Selection.Style = "Percent"
Columns("N:N").Select
Selection.NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
Columns("N:N").EntireColumn.AutoFit
Range("M:M,K:K,J:J,F:F").Select

What code would I use to popup the save as window at the end of my code?

Thanks for the help

 

[polska2180]

okay i just figured it out myself.

Dim DestinationRng, OriginalRng As Range
Dim end_row As Integer
end_row = Sheets("stats").Range("a65536").End(xlUp).Row
Set DestinationRng = Sheets("stats").Range("H2:H" & end_row)
Set OriginalRng = Range("H2")
OriginalRng.AutoFill Destination:=DestinationRng, Type:=xlFillDefault

I had three columns that i needed to autofill. I used the code above for all three however i would copy and paste the whole thing the error would show up when it got to the bolded part as it repeats 3x. I just need it once doh!

I appreciate everybodys help.

 

[Norie]

polska

Glad you got it sorted, and that you posted back with the solution.

One note though - are you sure you need to repeat the code 3 times?

Are the columns adjacent?

Dim DestinationRng As Range, OriginalRng As Range
Dim end_row As Long
end_row = Sheets("stats").Range("a65536").End(xlUp).Row
Set OriginalRng = Range("H2:J2")
Set DestinationRng = Sheets("stats").Range("H2:J" & end_row)
OriginalRng.AutoFill Destination:=DestinationRng, Type:=xlFillDefault

 

[pooj_malh]

Hi,

Please find the modified code

Sub Time_macro()
'
' Time_macro Macro
' Macro recorded 9/27/2006 by Herbst
'

'
Dim LR As Long

'  "to the end of the block of data I'm in"

LR = ActiveSheet.Range("A65536").End(xlUp).Row

    Range("C22").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=RC1+R16C2/86400"
    Range("C22").Select
    Selection.NumberFormat = "h:mm:ss.000 AM/PM"
    Range("C22").Select
    Selection.ClearContents
    ActiveCell.FormulaR1C1 = "=R16C2+RC1/86400"
    Range("C22").Select
    Selection.AutoFill Destination:=Range("C22:C" & LR)
    Range("C22:C521").Select
End Sub

 

[polska2180]

polska

Glad you got it sorted, and that you posted back with the solution.

One note though - are you sure you need to repeat the code 3 times?

Are the columns adjacent?

Dim DestinationRng As Range, OriginalRng As Range
Dim end_row As Long
end_row = Sheets("stats").Range("a65536").End(xlUp).Row
Set OriginalRng = Range("H2:J2")
Set DestinationRng = Sheets("stats").Range("H2:J" & end_row)
OriginalRng.AutoFill Destination:=DestinationRng, Type:=xlFillDefault

No they are not adjacent and they are formated different ways at different times. Although i'm sure there is a more efficient way to write this especially since most of it is created with a macro recorder the whole thing is going to be so small it will not make a difference so I don't want to invest my tme into making it so. I appreciate the suggestion though.