VBA/Macro issue

[Fester675]

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!!

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

____________________________________________________________________________________

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

 

[jasonb75]

You can trim post 7 down a bit

Sub Macro2_Export()
Intersect(Range("F:J,L:L,N:O,Q:S,U:V,X:X,Z:Z,AB:AC,AE:AE,AG:AG,AI:AJ,AL:AL,AN:AN,AP:AQ,AS:AS,AU:AU,AW:AX,AZ:AZ,BB:BB,BD:BE,BG:BG"), Rows("6:" & Cells(Rows.Count, "D").End(xlUp).Row)).Copy
End Sub

 

[footoo]

You can trim post 7 down a bit

Sub Macro2_Export()
Intersect(Range("F:J,L:L,N:O,Q:S,U:V,X:X,Z:Z,AB:AC,AE:AE,AG:AG,AI:AJ,AL:AL,AN:AN,AP:AQ,AS:AS,AU:AU,AW:AX,AZ:AZ,BB:BB,BD:BE,BG:BG"), Rows("6:" & Cells(Rows.Count, "D").End(xlUp).Row)).Copy
End Sub

At the expense of readability without gaining anything.

 

[jasonb75]

I didn't say that it gains anything, only that it could be trimmed down.

It's easy enough to read, if you find it difficult you could always split it with an underscore between Range() and , Rows()

I would say that your use of End(3).Row instead of End(xlUp).Row is a bigger readability issue, there are many people who would not know what the (3) does, but (xlUp) is fairly self explanatory.

 

[footoo]

"a bigger readability issue"

 

[jasonb75]

I came here to provide a suggestion, not to bicker, but you are correct by quoting what I said earlier, the line of code that I posted might not fit on the screen without scrolling, but at least it is all on the same page. With yours, anyone who doesn't know what End(3) means would have to search for information. I just tried that and nothing useful comes up, however the one thing that I did find is that End(3) is bad syntax and that the correct version is End(-4162).Row

So in summary, I would say that using incorrect, practically undocumented syntax is a big readability issue for anyone who wants to try and understand what you've posted.

 

[footoo]

"a big readability issue"?

 

[footoo]

For anyone who wants to save on keystrokes (or needs to because of restricted manual dexterity), and doesn't mind writing code with a big readability issue , this might be helpful :
1 xlLeft
2 xlRight
3 xlUp
4 xlDown

 

[jasonb75]

For anyone who wants to save on keystrokes

Thanks for justifying my suggestion, I believe that my readability sacrifice saves around 50 keystrokes while yours only saves 3

 

[Fester675]

Thanks guys!!
The export works great - can I now use the same code to CLEAR, by just adding CLEARCONTENTS at the end (obviously by using applicable columns)?

 

[jasonb75]

Yes you can, both tasks work on the same principle, with just the final action being different.