Problem with copying data over from different spreadsheet

p45cal · #2 08-01-2024, 03:39 AM

As an exercise, set up a new sheet with values 1 to 10 in cells C5 and down:
2024-08-01_110939.jpg

Here's the code to copy/paste:

Code:

Sub blah()
For Each empCell In Range(Range("C5"), Range("C5").End(xlDown))
  empCell.Select
  empCell.EntireRow.Delete
Next empCell
End Sub

Now use F8 on the keyboard to step through the code, paying particular attention to what's selected (to indicate what's going to be acted upon). The second iteration of the loop doesn't select what you expect; it selects the 3 instead of the 2! This is because the rows have all shifted up when a row is deleted.

Let the macro run to its finish and you're left with:
2024-08-01_111844.jpg

There are several ways to get around this, one of them being to iterate from the bottom of the range up:

Code:

Sub blah2()
Set rngEmpNames = Range(Range("C5"), Range("C5").End(xlDown))
For i = rngEmpNames.Rows.Count To 1 Step -1
  rngEmpNames.Cells(i).Select
  rngEmpNames.Cells(i).EntireRow.Delete
Next i
End Sub

but this means you end up pasting things in a different order on the destination sheet.

Another way is to iterate as you have done but do no deleting. Instead, mark the rows for deletion later. This doesn't involve actual marking, instead you add the cells, whose rows you want to delete, to a range variable in the code, and when all the copying has been completed, delete those rows.
For example:

Code:

Sub blah3()
Dim rngToDelete As Range
For Each empCell In Range(Range("C5"), Range("C5").End(xlDown))
  empCell.Select  'you can lose this line later
  If Application.WorksheetFunction.IsEven(empCell.Row) Then 'just a way of not deleting all rows
    If rngToDelete Is Nothing Then Set rngToDelete = empCell Else Set rngToDelete = Union(rngToDelete, empCell)
  End If
Next empCell
'rngToDelete.Select ' you can include this line if you're stepping through the code with F8 just to show you what rngToDelete is.
If Not rngToDelete Is Nothing Then rngToDelete.EntireRow.Delete
End Sub

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Copying data from one spreadsheet to another	muna	Excel	1	12-11-2017 05:23 AM
calculate spreadsheet data	ewso	Excel Programming	3	07-30-2017 07:25 AM
Copying rows from one spreadsheet to another	Colonel Biggs	Excel	3	02-15-2015 08:23 AM
Copying an Excel spreadsheet into Word with gridlines and a page break	Cheryl	Word	2	09-04-2014 06:24 AM
Creating Invoices using data from a spreadsheet	tjcusick	Excel Programming	3	12-05-2013 08:36 AM