After exporting a table from Access into Excel, I need to move the data down
45 rows via automation.

This is working, but seems inefficient:

Do While fr < 45
xlapp.Workbooks(strXlsFile).Worksheets(sn).Cells(f r + 1, "A"). _
EntireRow.Insert shift:=xlDown
fr = fr + 1
Loop

Is there a better way to move this contiguous block of cells? There is no
other data in the worksheet, and the cells always begin at A1.

Thanks in advance.

To cut and move:

ActiveSheet.UsedRange.Cut _
Destination:=Range("A45")


To move and clear:

Dim x As Integer
x = ActiveSheet.UsedRange.Rows.Count
ActiveSheet.UsedRange.Copy _
Destination:=Range("A45")
Range(Rows(1), Rows(x)).ClearContents

To move values and clear:

Dim x As Integer
x = ActiveSheet.UsedRange.Rows.Count

ActiveSheet.UsedRange.Offset(45, 0).Value = ActiveSheet.UsedRange.Value
Range(Rows(1), Rows(x)).ClearContents

--
steveB

Remove "AYN" from email to respond
"deko" wrote in message
. com...
After exporting a table from Access into Excel, I need to move the data
down
45 rows via automation.

This is working, but seems inefficient:

Do While fr < 45
xlapp.Workbooks(strXlsFile).Worksheets(sn).Cells(f r + 1, "A"). _
EntireRow.Insert shift:=xlDown
fr = fr + 1
Loop

Is there a better way to move this contiguous block of cells? There is no
other data in the worksheet, and the cells always begin at A1.

Thanks in advance.

have you tried?

Sub InsertRows()
Rows("1:44").Insert
End Sub


--
Don Guillett
SalesAid Software

"deko" wrote in message
. com...
After exporting a table from Access into Excel, I need to move the data
down
45 rows via automation.

This is working, but seems inefficient:

Do While fr < 45
xlapp.Workbooks(strXlsFile).Worksheets(sn).Cells(f r + 1, "A"). _
EntireRow.Insert shift:=xlDown
fr = fr + 1
Loop

Is there a better way to move this contiguous block of cells? There is no
other data in the worksheet, and the cells always begin at A1.

Thanks in advance.

Sub InsertRows()
Rows("1:44").Insert
End Sub

That was easy....

xlapp.Workbooks(strXlsFile).Worksheets(sn).Rows("1 :44").Insert

Thanks!

glad to help

--
Don Guillett
SalesAid Software

"deko" wrote in message
om...
Sub InsertRows()
Rows("1:44").Insert
End Sub

That was easy....

xlapp.Workbooks(strXlsFile).Worksheets(sn).Rows("1 :44").Insert

Thanks!