VBA/Macro issue

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Fester675

Board Regular
Joined
Sep 16, 2016
Messages
116
AArrgghh!!
Export works fine, necessary cells protected - but then CLEAR doesn't work!
Error 400 (?)
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
7,965
Office Version
2019
Platform
Windows
Does it give any other information? Is there any other code in the workbook, a worksheet change event for example?

RTE 400 is a bit of an anomaly for what you're doing, I think that error 1004 would be the most likely one to occur if it is being caused by the clearcontents code.

If you were using footoo's method, which line of code highlights when you hit debug?

Do both methods (mine and footoo's) cause the same error?
 

Fester675

Board Regular
Joined
Sep 16, 2016
Messages
116
I'll check it later Jason & get back to you. Thank you so much for your help, it really is appreciated 👍🏼
 

Fester675

Board Regular
Joined
Sep 16, 2016
Messages
116
Hi jasonb75 - It is a runtime error 1004, on the last line.
Intersect(rng.EntireColumn, Rows("6:" & Cells(Rows.Count, "D").End(3).Row)).ClearContents
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
7,965
Office Version
2019
Platform
Windows
Could you post the entire code exactly as you're using it please. It might be because the morning caffeine hasn't kicked in yet, but I'm not seeing anything wrong with that specific line, so best guess would be a problem in the line where you define rng which is not being picked up until a later stage.

I don't recall which error type occurs when you try to clear protected cells, so potentially that could be the cause.
 

Fester675

Board Regular
Joined
Sep 16, 2016
Messages
116
Sub Clear()
' Clears all relevant cells
Dim rng As Range
Set rng = Range("R6,F6:J6,L6,N6:O6,Q6,S6,U6:V6,X6,Z6,AB6:AC6,AE6,AG6,AI6:AJ6," _
& "AL6,AN6,AP6:AQ6,AS6,AU6,AW6:AX6,AZ6,BB6,BD6,BE6,BG6")
Intersect(rng.EntireColumn, Rows("6:" & Cells(Rows.Count, "D").End(3).Row)).ClearContents
End Sub

But what I've just noticed is, these are the first row of cells in the columns that require CLEAR - the range for each column goes from D6 to D255. This is due to having formulae to auto-fill the other cells based on if something is in D6 or not. This is replicated throughout various columns within the sheet.
D6,E6,F6,H6,K6,M6,O6,P6,R6,T6,V6,W6,Y6,AA6,AC6,AD6,AF6,AH6,AJ6,AK6,AM6,AO6,AQ6,AR6,AT6,AV6,AX6,AY6,BA6,BC6,BE6,BF6
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
7,965
Office Version
2019
Platform
Windows
Not sure why that is happening, clear and clearcontents are different, you should be able to clearcontents on all of it.

Clearcontents removes data but not formatting, clear removes data and formatting.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
7,965
Office Version
2019
Platform
Windows
A quick search comes up with merged cells as a potential cause, although I would have thought that you would have encountered the same issue with your recorded macro if that was the case here, unless you have merged them since.
 

Fester675

Board Regular
Joined
Sep 16, 2016
Messages
116
Sorted!! ;)

Sub Clear()
' Clears all relevant cells
Dim rng As Range
Set rng = Range("D6,E6,F6,H6,K6,M6,O6,P6,R6,T6,V6,W6,Y6,AA6,AC6," _
& "AD6,AF6,AH6,AJ6,AK6,AM6,AO6,AQ6,AR6,AT6,AV6,AX6," _
& "AY6,BA6,BC6,BE6,BF6")
Intersect(rng.EntireColumn, Rows("6:" & Cells(Rows.Count, "D").End(3).Row)).ClearContents
End Sub
 

Forum statistics

Threads
1,089,217
Messages
5,406,916
Members
403,111
Latest member
Donbozone

This Week's Hot Topics

Top