CODEWITHSUNDEEP
excelvba
GalacticPonderer
GalacticPonderer

Reputation: 547

Selecting Range

TL;DR: I would like it so that the range A6:A? down is pasted into range B10:B?

I am trying to select a range of data to populate into another column. The range is coming from a table and is currently in range A6:A9, although this will extend.

The code that I am currently using is:

Sub Supplier_Names()

Dim wb As Workbook
Dim ws_Temp As Worksheet
Dim Supplier_Names As Range

Set wb = ThisWorkbook
Set ws_Temp = wb.Worksheets("Templates")

Set Supplier_Names = ws_Temp.Range("A6", Range("A6").End(xlDown))

ws_Temp.Range("B10") = Supplier_Names


End Sub

This works when I only select from range A6, but when I run it with the xlDown it does not enter any values into B10. It also does not come up with a debug box.

Any solutions would be great!

Cheers,

Upvotes: 0

Views: 130

Answers (3)

Dy.Lee
Dy.Lee

Reputation: 7567

Using array is easy.

Sub Supplier_Names()

    Dim wb As Workbook
    Dim ws_Temp As Worksheet
    Dim Supplier_Names As Range
    Dim vDB As Variant
    
    Set wb = ThisWorkbook
    Set ws_Temp = wb.Worksheets("Templates")
    With ws_Temp
        Set Supplier_Names = ws_Temp.Range("A6", .Range("A6").End(xlDown))
        vDB = Supplier_Names '<~~ use array
        .Range("b10").Resize(UBound(vDB, 1)) = vDB
    End With

End Sub

The following are the same. It doesn't matter which one is a character and which one is a range.

Dim rngDB As Range

Set rngDB = Range(Range("a1"), Range("a10"))

Set rngDB = Range("a1", "a10")

Set rngDB = Range("a1", Range("a10"))

Set rngDB = Range("a1:a" & 10)

Upvotes: 2

VBasic2008
VBasic2008

Reputation: 54777

I've introduced another range object to make it more readable:

Option Explicit

Sub Supplier_Names()

    Dim wb As Workbook
    Dim Temp As Worksheet
    Dim Supp As Range
    Dim Cust As Range
    
    Set wb = ThisWorkbook
    Set Temp = wb.Worksheets("Templates")
    
    Set Supp = Temp.Range("A6")
    Set Supp = Temp.Range(Supp, Supp.End(xlDown))
    
    Set Cust = Temp.Range("B10")
    Set Cust = Cust.Resize(Supp.Rows.Count, Supp.Columns.Count)
    
    Cust.Value = Supp.Value

End Sub

Upvotes: 1

Samuel Everson
Samuel Everson

Reputation: 2102

Range("A1").End(xlDown) returns the default property which is Value.

So for example, your current range is A6:A9, if A9 has the word Test in it, your line becomes:
Set Supplier_Names = ws_Temp.Range("A6", "Test"). though it should be noted Excel/VBA seems smart enough to work out what you are wanting to do and does infact use the cell address reference)

You can use the Address property of Range to return the cell address that is found.

Like so:

Set Supplier_Names = ws_Temp.Range("A6", Range("A6").End(xlDown).Address)

Which would translate to:

Set Supplier_Names = ws_Temp.Range("A6", "$A$9")

I agree with @Dy.Lee answer that using an Array is a better way to do what you are wanting to do.

Like so:

Sub Supplier_Names()

Dim ws_Temp As Worksheet
Dim Supplier_Names_Array() As Variant

Set ws_Temp = ThisWorkbook.Worksheets("Templates")

With ws_Temp
    Supplier_Names_Array = .Range("A6", .Range("A6").End(xlDown).Address)
    .Range("B10").Resize(UBound(Supplier_Names_Array, 1)) = Supplier_Names_Array
End With
End Sub

http://www.cpearson.com/Excel/ArraysAndRanges.aspx is a great resource for learning about this method of using Arrays with the Worksheet.

Upvotes: 1

Related Questions