macro issue - autofill to last row rather than defined number of rows required

littlevic

New Member
Joined
Jan 14, 2011
Messages
12
I have written this macro which converts data from a gas supplier into a format that we can upload into a database. The number of rows is different every month, so every month I need to change the numbers in the macro. Is there anyway to get this to autofill to the number of rows in column A?

Sub TGPData()
'
' TGPData Macro
'

'
Columns("A:A").Select
Selection.Delete Shift:=xlToLeft
Selection.ColumnWidth = 8.57
Columns("A:D").Select
Columns("A:D").EntireColumn.AutoFit
Columns("D:D").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("D1").Select
ActiveCell.FormulaR1C1 = "Reading"
Range("D2").Select
ActiveCell.FormulaR1C1 = "=(INT(RC[-1]/100)+(MOD(RC[-1]/100,1)*100)/60)/24"
Range("D2").Select
Selection.AutoFill Destination:=Range("D2:D91815")
Range("D2:D91815").Select
Selection.NumberFormat = "h:mm:ss"
Columns("E:E").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("E2").Select
ActiveCell.FormulaR1C1 = "=(RC[-3]+RC[-1])"
Range("E2").Select
Selection.NumberFormat = "m/d/yyyy h:mm"
Selection.AutoFill Destination:=Range("E2:E91815")
Range("E2:E91815").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("B:D").Select
Range("D1").Activate
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Range("B1").Select
ActiveCell.FormulaR1C1 = "Reading"
Range("C1").Select
ActiveCell.FormulaR1C1 = "kWh"
Columns("A:C").Select
Range("C1").Activate
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

End Sub

I tried this one

Selection.Autofill Destination:=Range("F1:F" & Range("A" & Rows.Count).End(xlUp).Row)

but it is bugged and when I step into the macro, the 2 lines that are bugged are highlighted in red and the reference to F in blue is obviously changed to D/E as necessary

thanks for your help, cheers :)
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

fredlo2008

Active Member
Joined
Jan 12, 2012
Messages
254
See if this works

Code:
Sub TGPData()'
' TGPData Macro


Dim irow As Long


Application.ScreenUpdating = False
irow = Range("A" & Rows.Count).End(xlUp).Row


Columns("A:A").Delete Shift:=xlToLeft
Columns("A:A").ColumnWidth = 8.57


Columns("A:D").EntireColumn.AutoFit


Columns("D:D").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove


Range("D1").Value = "Reading"


Range("D2:D" & irow).FormulaR1C1 = "=(INT(RC[-1]/100)+(MOD(RC[-1]/100,1)*100)/60)/24"
Range("D2:D" & irow).NumberFormat = "h:mm:ss"


Columns("E:E").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove


Range("E2:E" & irow).FormulaR1C1 = "=(RC[-3]+RC[-1])"
Range("E2:E" & irow).NumberFormat = "m/d/yyyy h:mm"


Range("E2:E" & irow).Value = Range("E2:E" & irow).Value


Columns("B:D").Delete Shift:=xlToLeft


Range("B1").Value = "Reading"
Range("C1").Value = "kWh"




With Columns("A:C")
    .HorizontalAlignment = xlLeft
    .VerticalAlignment = xlBottom
End With


Application.ScreenUpdating = True
End Sub
 

littlevic

New Member
Joined
Jan 14, 2011
Messages
12
See if this works

Code:
Sub TGPData()'
' TGPData Macro


Dim irow As Long


Application.ScreenUpdating = False
irow = Range("A" & Rows.Count).End(xlUp).Row


Columns("A:A").Delete Shift:=xlToLeft
Columns("A:A").ColumnWidth = 8.57


Columns("A:D").EntireColumn.AutoFit


Columns("D:D").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove


Range("D1").Value = "Reading"


Range("D2:D" & irow).FormulaR1C1 = "=(INT(RC[-1]/100)+(MOD(RC[-1]/100,1)*100)/60)/24"
Range("D2:D" & irow).NumberFormat = "h:mm:ss"


Columns("E:E").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove


Range("E2:E" & irow).FormulaR1C1 = "=(RC[-3]+RC[-1])"
Range("E2:E" & irow).NumberFormat = "m/d/yyyy h:mm"


Range("E2:E" & irow).Value = Range("E2:E" & irow).Value


Columns("B:D").Delete Shift:=xlToLeft


Range("B1").Value = "Reading"
Range("C1").Value = "kWh"




With Columns("A:C")
    .HorizontalAlignment = xlLeft
    .VerticalAlignment = xlBottom
End With


Application.ScreenUpdating = True
End Sub


thank you so much that is spot on
 

Watch MrExcel Video

Forum statistics

Threads
1,123,494
Messages
5,602,004
Members
414,490
Latest member
Rip181

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
Top