0

I paste data into an Excel worksheet. I and am trying to create a macro that will clean up the data.

Some days the data I need starts on line 20 and some days it might start on 21 or 22. The only constant is that the data I need to start from is always labeled "1" in column A.

I cobbled together some code. (My base code is from the record macro action. I made adjustments based on google searches.)

Dim row_num As Integer

Sheets("AMD").Select
ActiveSheet.Paste
    
'initialize variable
row_num = Sheets("CopyPaste").Range("F2").Value

Sheets("AMD").Range(Cells(1), Cells(row_num)).Select
Selection.Delete Shift:=xlUp
Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select

I get

Run-time error '1004': Method '_Default' of object 'Range' failed.

Debug sends me to:

Sheets("AMD").Range(Cells(1), Cells(row_num)).Select

The integer from 'CopyPaste'!F2 is the number of rows I need to delete.

Community
  • 1
  • 1
Michel
  • 25
  • 1
  • 7
  • 2
    See [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba?rq=1) – Алексей Р May 26 '21 at 13:33
  • 2
    Cells needs both the row and column number as inputs like `Cells(row_num, col_num)`. If you just need to select some rows you can put that in like `Range("2:8")` which refers to rows 2 to 8. If you want it dynamic you can do `Range( first_row & ":" & last_row)` – Toddleson May 26 '21 at 13:33
  • They're not blank. It's data I'm copying from an internal web database. The rows always have the same information, but depending on how many serials I'm researching changes how many rows of extraneous data there could potentially be. – Michel May 26 '21 at 13:36
  • 4
    `Sheets("AMD").Rows("1:" & row_num).Delete` – Алексей Р May 26 '21 at 13:37
  • And about error 1004. If you're select range in the non-active sheet, this error occurs – Алексей Р May 26 '21 at 13:39
  • Why have the number of rows to delete listed? I thought the logic was that if Column A = 1 , then Delete the row? Is that not the case here? – Mark S. May 26 '21 at 13:44
  • Thank you @Toddleson that worked simply and beautifully. – Michel May 26 '21 at 13:48
  • Thank you @Алексей Р, I'll have to look through that "avoid using Select" article later. – Michel May 26 '21 at 13:48

0 Answers0