VBA/Macro issue

Fester675

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

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,295
Office Version
  1. 2016
Platform
  1. Windows
Tidied-up :
VBA Code:
Sub Macro2_Export()
' Copies pre-determined cells to clipboard ready to paste into Oracle Loader.
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").Copy
Range("D6").Select
End Sub
____________________________________________________________________________________
Sub Macro1_Clear()
' Clears contents from relevant cells.
Range("BF6,D6: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,BE6").ClearContents
Range("D6").Select
End Sub

"how do I add in an extra piece of code to select extra rows dependent on whether they are empty or not?"
More info required - what columns and based on what criteria?
What do you want to do with these "extra rows"?
 

Fester675

Board Regular
Joined
Sep 16, 2016
Messages
131
Oh my! That's fantastic footoo!!

"how do I add in an extra piece of code to select extra rows dependent on whether they are empty or not?"
More info required - what columns and based on what criteria?
What do you want to do with these "extra rows"?

Ok - so I have been testing in one row only. I need to now duplicate the full row to approximately 200 rows - It's to run in conjunction with the VBA above - so within 'Export' I want it to start at D6 and check each subsequent row in the same column, if it has an entry then COPY row 7 for the same cells as row 6.
And the same with the Clear. Hope it makes sense!
 

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,295
Office Version
  1. 2016
Platform
  1. Windows
• Is there any data in rows below the relevant columns' last row with data?
If not :
VBA Code:
Sub Macro1_Clear()
' Clears contents from relevant cells.
Dim rng As Range
Set rng = Range("D6: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,BE6,BF6")
Intersect(rng.EntireColumn, Rows("6:" & Rows.Count)).ClearContents
End Sub
• If the above is not suitable, is there a column that can be used to determine the last data row for the relevant columns?

For the other macro, some questions :
• Do you mean that you want to check column D only and if it is not blank, copy the relevant cells for that row?
• Are there any formulas in the relevant cells?
• If there are formulas, any that can return "" ?
 

footoo

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

ADVERTISEMENT

In previous post, ignore this :
• Is there any data in rows below the relevant columns' last row with data?
If not :

and this :
• If the above is not suitable, is there a column that can be used to determine the last data row for the relevant columns?
 

Fester675

Board Regular
Joined
Sep 16, 2016
Messages
131
Thanks footoo.

For the other macro, some questions :
• Do you mean that you want to check column D only and if it is not blank, copy the relevant cells for that row? Yes. As this column/row will be the first cell to be filled. And it is mandatory, so it's blank until used.

• Are there any formulas in the relevant cells? Yes.
• If there are formulas, any that can return "" ? Yes.
 

footoo

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

ADVERTISEMENT

• Do you mean that you want to check column D only and if it is not blank, copy the relevant cells for that row? Yes. As this column/row will be the first cell to be filled. And it is mandatory, so it's blank until used.

Does that mean that column D has no gaps in the data?
If so :
VBA Code:
Sub Macro2_Export()
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)).Copy
End Sub
 

Fester675

Board Regular
Joined
Sep 16, 2016
Messages
131
'D' is blank until a new row needs to be started, whereby 'D' will be the first cell to be completed
 

Fester675

Board Regular
Joined
Sep 16, 2016
Messages
131
I'll check it as soon as I can footoo, and let you know. I'm UK time so might not be until after lunch.
But massive thanks nonetheless!!
 

Forum statistics

Threads
1,141,299
Messages
5,705,574
Members
421,399
Latest member
hjweiss00

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