delete row after last data row

[steve hill]

Hi I have recorded a macro that update a query and copies four columns of formulas down beside the query table. then updates another query. what i need to do is add something that will delete all the rows after the last row of data in the first query. the total number of rows chages every time, But I have only copied the formulas down to row 1000. I have copied the code procuced by the macro below and added a comment were I need to delet the rows.

Sub updatereport()
'
' updatereport Macro
'

'
Sheets("grns").Select
Range("A1").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Range("E2:H2").Select
Selection.AutoFill Destination:=Range("E2:H1000"), Type:=xlFillDefault
Range("E2:H1019").Select
'add code to delete all row after last data on column A
Sheets("NCR").Select
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
Sheets("report").Select
Range("A1").Select
End Sub

thanks
steve

 

[JackDanIce]

See added bits in red:

Sub updatereport()
'
' updatereport Macro
 
Sheets("grns").Select
Range("A1").QueryTable.Refresh BackgroundQuery:=False
Range("E2:H2").AutoFill Destination:=Range("E2:H1000"), Type:=xlFillDefault
 
Dim i As Long
i = Range("A" & Rows.Count).End(xlUp).Row + 1
Range(Range("A" & i), Range("A" & i).End(xlDown)).EntireRow.Delete
 
Sheets("NCR").Select
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
Sheets("report").Select
Range("A1").Select
 
End Sub

 

[steve hill]

thanks for quick reply
I got an error
runtime 438 object doent support this property or method

the highlighted row in debubber was

Range(Range("A" & i), Range("A" & i).End(xlDown)).entirerows.Delete

Steve

 

[JackDanIce]

I editted my post after I replied, that line should read:

Range(Range("A" & i), Range("A" & i).End(xlDown)).EntireRow.Delete

I'd made a typo and put an 's' after row

Error: .EntireRows.Delete

 

[steve hill]

thanks that has worked a treat

Steve

 

[JackDanIce]

You're welcome