Selecting Column Until End of Another Array

[giraffe3289]

Hi all,

I'm importing data from an access database into a spreadsheet. Every time I refresh the data it has the potential to vary in number of entries/length. One field (active doctors) will ALWAYS be filled in but some of the other fields may have blank entries (malpractice insurance). I've selected the active doctors by selecting until there is a blank cell and put it into an array:

Dim DocListName As Variant

' Selects active practitioner list with last name, first name, and title
Range("B2").Select
DocListName = Range(Selection, Selection.End(xlDown)).Value

But then I need to select the same number of malpractice insurance entries as the active doctors without stopping at blank cells. The code I have now is similar to above and is as follows:

Dim MalPrac As Variant

' Selects malpractice insurance list
Range("N2").Select
MalPrac = Range(Selection, Selection.End(xlDown)).Value

However, if there are blank entries, it stops at those cells and doesn't fill the array to the same length as the active doctors array.

Thanks for all your help,

Giraffe3289

[Bob Phillips]

Dim LastRow As Long

' Selects active practitioner list with last name, first name, and title
LastRow = Cells(Rows.Count,"B").End(xlUp).Row
DocListName = Cells(LastRow, "B").Value

' ...

Dim MalPrac As Variant

' Selects malpractice insurance list
MalPrac = Cells(LastRow, "N").Value

[Aussiebear]

I believe that your method of selecting the range could be causing you the problem
Range (Selection,Selection.End(xlDown)).Value

If there are blanks in the range, Excel will stop at the first occurance of a blank, thinking that this is the end of the range. An example of finding the last cell used in range (column) would be as follows
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

[Aussiebear]

Hmm... I'm going to have to refresh more often. Sorry Bob.

[giraffe3289]

So I set up the LastRow stuff and it correctly identifies the LastRow of the data, but how do I make a range that goes to the LastRow? Right now I have:

' Finds the last row of the column
LastRow = Cells(Rows.Count, "B").End(xlUp).Row

' Selects active practitioner list with last name, first name, and title
DocListName = Range("B2", LastRow).Value

But then I get an error message. Any ideas?

Thanks,

Giraffe3289

[mdmackillop]

Either

Sub Test()
Dim DocListName
Dim LastRow As Range
Set LastRow = Cells(Rows.Count, "B").End(xlUp)
DocListName = Range("B2", LastRow).Value
End Sub

or

Sub Test2()
Dim DocListName
Dim LastRow As Long
LastRow = Cells(Rows.Count, "B").End(xlUp).Row
DocListName = Range("B2", "B" & LastRow).Value
End Sub

[Bob Phillips]

If you want a range, use



Set rng = Range("N2").Resize(LastRow-1)

[giraffe3289]

That worked wonderfully. Thank you so much for all your help and for responding so quickly! :cloud9: