VBA/Macro issue

Fester675

Board Regular
Joined
Sep 16, 2016
Messages
129
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:

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,926
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,259
Office Version
  1. 2016
Platform
  1. Windows
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.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,926
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Sep 21, 2016
Messages
3,259
Office Version
  1. 2016
Platform
  1. Windows
"a bigger readability issue" :)
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,926
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Sep 21, 2016
Messages
3,259
Office Version
  1. 2016
Platform
  1. Windows
"a big readability issue"🤣
 

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,259
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Dec 30, 2008
Messages
12,926
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Sep 16, 2016
Messages
129
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

Well-known Member
Joined
Dec 30, 2008
Messages
12,926
Office Version
  1. 365
Platform
  1. Windows
Yes you can, both tasks work on the same principle, with just the final action being different.
 

Watch MrExcel Video

Forum statistics

Threads
1,132,898
Messages
5,655,856
Members
418,248
Latest member
JinxedCaspa

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
Top