VBA/Macro issue

Fester675

Board Regular
Joined
Sep 16, 2016
Messages
125
I have recorded 2 Macros - one to copy a range of cells to the clipboard; the second to clear the relevant cells to start over.
I am a novice at VBA, so wondered how do I tidy things up; and how do I add in an extra piece of code to select extra rows dependent on whether they are empty or not?

TIA!!

VBA Code:
Sub Macro2_Export()
'
' Macro2_Export Macro
' Copies pre-selected cells to clipboard ready to paste into Oracle Loader.
'

'
    Range("R6,F6:J6,L6,N6:O6,Q6,S6").Select
    Range("S6").Activate
    ActiveWindow.LargeScroll ToRight:=1
    Range("R6,F6:J6,L6,N6:O6,Q6,S6,U6:V6,X6,Z6,AB6:AC6,AE6,AG6,AI6:AJ6,AL6").Select
    Range("AL6").Activate
    ActiveWindow.LargeScroll ToRight:=1
    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" _
        ).Select
    Range("BD6").Activate
    ActiveWindow.LargeScroll ToRight:=1
    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" _
        ).Select
    Range("BG6").Activate
    Selection.Copy
    ActiveWindow.ScrollColumn = 55
    ActiveWindow.ScrollColumn = 54
    ActiveWindow.ScrollColumn = 53
    ActiveWindow.ScrollColumn = 51
    ActiveWindow.ScrollColumn = 50
    ActiveWindow.ScrollColumn = 47
    ActiveWindow.ScrollColumn = 44
    ActiveWindow.ScrollColumn = 40
    ActiveWindow.ScrollColumn = 36
    ActiveWindow.ScrollColumn = 31
    ActiveWindow.ScrollColumn = 27
    ActiveWindow.ScrollColumn = 22
    ActiveWindow.ScrollColumn = 18
    ActiveWindow.ScrollColumn = 14
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 1
    Range("D6").Select
End Sub
____________________________________________________________________________________
VBA Code:
Sub Macro1_Clear()
'
' Macro1_Clear Macro
' Selects relevant cells & clears contents.
'

'
    Range("D6,E6,F6,H6,I6,K6,M6,O6,P6,R6,T6").Select
    Range("T6").Activate
    ActiveWindow.LargeScroll ToRight:=1
    Range("D6,E6,F6,H6,I6,K6,M6,O6,P6,R6,T6,V6,W6,Y6,AA6,AC6,AD6,AF6,AH6,AJ6,AK6"). _
        Select
    Range("AK6").Activate
    ActiveWindow.LargeScroll ToRight:=1
    ActiveWindow.SmallScroll ToRight:=-1
    Range( _
        "D6,E6,F6,H6,I6,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" _
        ).Select
    Range("BC6").Activate
    ActiveWindow.LargeScroll ToRight:=1
    Union(Range( _
        "BF6,D6,E6,F6,H6,I6,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" _
        ), Range("BE6")).Select
    Range("BF6").Activate
    Selection.ClearContents
    Range("D6").Select
End Sub
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Fester675

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

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,046
Office Version
  1. 2019
Platform
  1. 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
125
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
125

ADVERTISEMENT

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
11,046
Office Version
  1. 2019
Platform
  1. 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
125

ADVERTISEMENT

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
11,046
Office Version
  1. 2019
Platform
  1. 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
11,046
Office Version
  1. 2019
Platform
  1. 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
125
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
 

Watch MrExcel Video

Forum statistics

Threads
1,114,486
Messages
5,548,347
Members
410,828
Latest member
A9Bosv3
Top