Runtime Error 1004

mharper90

Board Regular
Joined
May 28, 2013
Messages
104
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,428
Office Version
365
Platform
Windows
If you place this line of code just before the one with the error, what does this return?
Code:
MsgBox ws2.UsedRange.Address
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,039
Office Version
365
Platform
Windows
I'm surprised it works at all.
Code:
Range("A:A"&rows.Count)
is trying to add the numbers of rows to the complete column.
Try
Code:
If ws2.Range("A" & Rows.count).End(xlUp).Row > 4 Then ws2.UsedRange.Offset(4).Clear
 

mharper90

Board Regular
Joined
May 28, 2013
Messages
104
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

Board Regular
Joined
May 28, 2013
Messages
104
I'm surprised it works at all.
Code:
Range("A:A"&rows.Count)
is trying to add the numbers of rows to the complete column.
Try
Code:
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

Board Regular
Joined
May 28, 2013
Messages
104
If you place this line of code just before the one with the error, what does this return?
Code:
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,428
Office Version
365
Platform
Windows
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

Board Regular
Joined
May 28, 2013
Messages
104
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,039
Office Version
365
Platform
Windows
How about
Code:
ws2.Rows("5:" & Rows.count).Delete
 

Forum statistics

Threads
1,081,664
Messages
5,360,361
Members
400,582
Latest member
SaD4775

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top