Runtime Error 1004

[mharper90]

I keep getting a Runtime Error 1004 with a particular macro. The debug option always take me back to the "UsedRange" portion of:

If ws2.Range("A:A" & Rows.Count).End(xlUp).Row > 4 Then ws2.UsedRange.Offset(4).Clear

What's so frustrating is that if I copy and paste the contents to a brand new worksheet, and copy and paste the macro as a new sub, then it works flawlessly. No change in code or worksheet data, just a refreshing of everything else. With no particular relativity, it seems to eventually cause the same Runtime Error 1004, to which the same corrective action will temporarily resolve it.

Does anyone know what could be causing this instability in what seems to be a functional code?

 

[Joe4]

If you place this line of code just before the one with the error, what does this return?

MsgBox ws2.UsedRange.Address

 

[Fluff]

I'm surprised it works at all.

Range("A:A"&rows.Count)

is trying to add the numbers of rows to the complete column.
Try

If ws2.Range("A" & Rows.count).End(xlUp).Row > 4 Then ws2.UsedRange.Offset(4).Clear

 

[mharper90]

Thanks for the feedback. I will try both out and let you know. (The file is at my work, without real internet access there.)

 

[mharper90]

I'm surprised it works at all.

Range("A:A"&rows.Count)

is trying to add the numbers of rows to the complete column.
Try

If ws2.Range("A" & Rows.count).End(xlUp).Row > 4 Then ws2.UsedRange.Offset(4).Clear

Sorry, the A:A was my typo when entering on the forum. It was just "A". Doesn't quite explain why the range is so funny (see next reply).

 

[mharper90]

If you place this line of code just before the one with the error, what does this return?

MsgBox ws2.UsedRange.Address

I got $A$1:$F$96, but I only expected to get A1:F8. Also, oddly, the 96 kept getting larger every time I ran the macro (I think by 2's), even though it was the exact same set of data in the range every time (A1:F8). Any ideas or resolutions?

 

[Joe4]

Used Range can be a little flaky if you are deleting data or formatting unused sections of your workbook.
What exactly is it that you want to clear?

If we have a clear understanding of that, we can probably offer alternatives that may work better.

 

[mharper90]

Perhaps I am overcomplicating it. This worksheet is essentially a roster of people and some data about them, with a 4 row header. The data is extracted with a macro from another larger roster, and so I want something at the beginning of the extraction macro that wipes this worksheet clean, except for the 4 row header.

Also, if it matters, the extraction macro also applies a signature line 4 blank rows after the last row of new data, and that starts in column C, so counting used rows could be tricky? (I don't know...still a noob).

Thanks!

 

[Fluff]

How about

ws2.Rows("5:" & Rows.count).Delete

 

[mharper90]

That worked perfectly! Thanks!