VBA/Macro issue

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
7,965
Office Version
2019
Platform
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
 

Some videos you may like

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.

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
2,828
Office Version
2016
Platform
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
7,965
Office Version
2019
Platform
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
2,828
Office Version
2016
Platform
Windows
"a bigger readability issue" :)
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
7,965
Office Version
2019
Platform
Windows
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
2,828
Office Version
2016
Platform
Windows
"a big readability issue"🤣
 

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
2,828
Office Version
2016
Platform
Windows
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
7,965
Office Version
2019
Platform
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
116
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
7,965
Office Version
2019
Platform
Windows
Yes you can, both tasks work on the same principle, with just the final action being different.
 

Forum statistics

Threads
1,089,218
Messages
5,406,923
Members
403,113
Latest member
ms_excel_recal_or_die

This Week's Hot Topics

Top