How to move cells with VBA?

  • Thread starter Thread starter deko
  • Start date Start date
D

deko

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(fr + 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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top