Varying Range - how will this effect my code?

G

geordie_ben

Board Regular
Joined
Jul 27, 2009
Messages
122
Apologies in advance for the basic question. I was slowly becoming a whizz at VBA a decade ago, but I'm picking it back up and need a few pointers

I'm making a macro to take info from sheet to another, and then to fill forumula that I've entered into the sheet already (that'll be a separate question to see if I can get the macro to insert the formula for me

The start of my code is:
Code: 
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Nominal Products").Select
    Range("B2").Select
    ActiveSheet.Paste
    Range("A2").Select
    Application.CutCopyMode = False
    Selection.AutoFill Destination:=Range("A2:A196")
    Range("A2:A196").Select
The next time I run the macro there may be more or less entries on sheet one. As above my macro states the range as A2:A196 - will this cause me any issues?
 
Last edited by a moderator:

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Sort by date Sort by votes
What determines the last cell A196? the last filled cell in column B?
 
Upvote 0
In this instance I've selected 195 lines (A2:A196) from the first sheet using the xldown function, I then need to insert them onto sheet two in column A starting at A2
 
Upvote 0
Your code is not selecting from column A in the first sheet and inserting (copying) to sheet two (Sheets("Nominal Products"). please use actual sheet names when asking questions).
It is inserting (copying) to sheet two (Sheets("Nominal Products") column B, more precisely B2.

So still need to know how you are defining the last cell in the destination sheet for the autofill of the formula to go down to in column A if the set up is as per your code. Please clarify.

Btw we can apply the formula directly to the range rather than use autofill if you let us know what the formula is in cell A2.
 
Upvote 0
Hi

Apologies. Hopefully this is a lot clearer

I started recording my macro on (Sheets("Subaccounts")

I then did the following

Code: 
Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Nominal Products").Select
    Range("B2").Select
    ActiveSheet.Paste
    Range("A2").Select
    Application.CutCopyMode = False
    Selection.AutoFill Destination:=Range("A2:A196")
    Range("A2:A196").Select



In plain terms, I copied all of the contents of column A minus the headers using CTRL+SHIFT+DOWN, and pasted it into column B on Sheets("Nominal Products")

On Sheets("Nominal Products") in A2 I have the following formula =SUBSTITUTE("Prod"&B2," ","")

I then clicked the Fill Handle to populate Column A

As Column B on Sheets("Nominal Products") goes down to B196, the range it column A followed suit and went down to A196

My issue is that the next time I run the macro there may only be 100 lines in (Sheets("Subaccounts") Column A

How can I make the range variable based on the number of lines selected in (Sheets("Subaccounts") Column A

I can share my worksheet with you if that makes it easier? If so, what's the best method?
 
Upvote 0
It still would be more efficient to apply the formula directly to the cells but as you haven't posted the formula try the code below....

Code: 
Sub XXX()
    Application.ScreenUpdating = False

    If Sheets("Nominal Products").Range("B" & Rows.Count).End(xlUp).Row > 2 Then _
       Sheets("Nominal Products").Range("A3:B" & Sheets("Nominal Products").Range("B" & Rows.Count).End(xlUp).Row).ClearContents

    With Sheets("Subaccounts")
        .Range(.Range("A2"), .Range("A2").End(xlDown)).Copy Sheets("Nominal Products").Range("B2")
    End With

    With Sheets("Nominal Products")
        .Range("A2").AutoFill Destination:=.Range("A2:A" & .Range("B" & Rows.Count).End(xlUp).Row)
    End With

    Application.ScreenUpdating = True
End Sub

Please note that as you are using xlDown you will get issues if you have any blank cells.
 
Last edited:
Upvote 0
Hi Mark, thanks for that

I want to try and work through it and figure out what each part is doing. I now remember the xlup part from when I used to do this ages ago, but still not sure how to write it. i'll research the components. i understand it goes to the bottom of the sheet and then goes back up to find the last entry in that column

As above, the first formula I need to add is =SUBSTITUTE("Prod"&B2," ","") which is currently stored in Sheets("Nominal Products") in A2, which I then just used the fill handle to populate

i have a few other formulas on Sheets("Nominal Products") but hopefully once I undertstand how to do one, I'll be able to do the rest
 
Upvote 0
Hi Mark

I'm cracking on with the code, thanks for your help so far

My next puzzle is I need to copy the contents of one worksheet, to another. I need to use a function like paste special values

I've found some VBA examples, but they're set up differently to the code you've posted

Here's my code so far

Code: 
Sub TimeSaver3()    Application.ScreenUpdating = False


    If Sheets("Nominal Products").Range("B" & Rows.Count).End(xlUp).Row > 2 Then _
       Sheets("Nominal Products").Range("A3:B" & Sheets("Nominal Products").Range("B" & Rows.Count).End(xlUp).Row).ClearContents


    With Sheets("Subaccounts")
        .Range(.Range("A2"), .Range("A2").End(xlDown)).Copy Sheets("Nominal Products").Range("B2")
    End With


    With Sheets("Nominal Products")
        .Range("A2").AutoFill Destination:=.Range("A2:A" & .Range("B" & Rows.Count).End(xlUp).Row)
    End With


    With Sheets("Nominal Products")
        .Range("C2").AutoFill Destination:=.Range("C2:C" & .Range("B" & Rows.Count).End(xlUp).Row)
    End With


    With Sheets("Nominal Products")
        .Range("D2").AutoFill Destination:=.Range("D2:D" & .Range("B" & Rows.Count).End(xlUp).Row)
    End With
    
    With Sheets("Nominal Products")
        .Range("E2").AutoFill Destination:=.Range("E2:E" & .Range("B" & Rows.Count).End(xlUp).Row)
    End With


    If Sheets("Nom Prod Dim Tags").Range("B" & Rows.Count).End(xlUp).Row > 2 Then _
       Sheets("Nom Prod Dim Tags").Range("A3:B" & Sheets("Nom Prod Dim Tags").Range("B" & Rows.Count).End(xlUp).Row).ClearContents


    With Sheets("Subaccounts")
        .Range(.Range("A2"), .Range("A2").End(xlDown)).Copy Sheets("Nom Prod Dim Tags").Range("B2")
    End With


    With Sheets("Nom Prod Dim Tags")
        .Range("A2").AutoFill Destination:=.Range("A2:A" & .Range("B" & Rows.Count).End(xlUp).Row)
    End With


    With Sheets("Nom Prod Dim Tags")
        .Range("C2").AutoFill Destination:=.Range("C2:C" & .Range("B" & Rows.Count).End(xlUp).Row)
    End With
    
    With Sheets("Nom Prod Dim Tags")
        .Range("E2").AutoFill Destination:=.Range("E2:E" & .Range("B" & Rows.Count).End(xlUp).Row)
    End With
    
    With Sheets("Companies")
        .Range("B2").Copy Sheets("Nom Prod Dim Tags").Range("D2")
    End With
    
    With Sheets("Nom Prod Dim Tags")
        .Range("D2").AutoFill Destination:=.Range("D2:D" & .Range("B" & Rows.Count).End(xlUp).Row)
    End With


    If Sheets("Nom Prod Dim Tag Legs").Range("A" & Rows.Count).End(xlUp).Row > 2 Then _
       Sheets("Nom Prod Dim Tag Legs").Range("A3:A" & Sheets("Nom Prod Dim Tag Legs").Range("A" & Rows.Count).End(xlUp).Row).ClearContents


    [B]With Sheets("Nom Prod Dim Tags")
        .Range(.Range("E2"), .Range("E2").End(xlDown)).Copy Sheets("Nom Prod Dim Tag Legs").Range("A2")
    End With[/B]


    With Sheets("Nom Prod Dim Tag Legs")
        .Range("B2").AutoFill Destination:=.Range("B2:B" & .Range("A" & Rows.Count).End(xlUp).Row)
    End With


    With Sheets("Nom Prod Dim Tag Legs")
        .Range("C2").AutoFill Destination:=.Range("C2:C" & .Range("A" & Rows.Count).End(xlUp).Row)
    End With
    
    With Sheets("Nom Prod Dim Tag Legs")
        .Range("D2").AutoFill Destination:=.Range("D2:D" & .Range("A" & Rows.Count).End(xlUp).Row)
    End With


    Application.ScreenUpdating = True
End Sub

It's the bold part I need to make it post paste special values
 
Upvote 0
Code: 
With Sheets("Nom Prod Dim Tags")
        .Range(.Range("E2"), .Range("E2").End(xlDown)).Copy 
         Sheets("Nom Prod Dim Tag Legs").Range("A2").Pastespecial xlPasteValues
    End With
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
VBA autofilling code
Replies
6
Views
603
Peter_SSs
Peter_SSs
C
  • Question Question
How to automatically paste the data from one sheet to another using VBA
Replies
4
Views
291
Alex Blakenburg
A
C
  • Solved
Please help me clean up my VBA script, I'm having trouble with redundancies
Replies
4
Views
453
CapRavOr
C
R
  • Solved
How to loop a macro
Replies
2
Views
580
rlcurlee
R
C
  • Question Question
Please help clean up my convoluted VBA code. It seems very redundant and repetitive.
Replies
1
Views
441
myall_blues
myall_blues

Forum statistics

Threads
1,224,823
Messages
6,181,175
Members
453,021
Latest member
Justyna P

Share this page

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