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

[littlevic]

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

 

[fredlo2008]

See if this works

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]

See if this works

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