VBA/Macro issue

Fester675

Board Regular
Joined
Sep 16, 2016
Messages
141
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:
You can trim post 7 down a bit
VBA Code:
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
 
Upvote 0

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
You can trim post 7 down a bit
VBA Code:
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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)?
 
Upvote 0
Yes you can, both tasks work on the same principle, with just the final action being different.
 
Upvote 0

Forum statistics

Threads
1,224,505
Messages
6,179,153
Members
452,891
Latest member
JUSTOUTOFMYREACH

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top