PDA

View Full Version : Solved: Range in VBA



lifeson
03-24-2007, 03:41 AM
Hi all
I have a named range on a sheet which is reffered to using the following
Range =OFFSET(BlrCopy!$A$1,1,0,(COUNTA(BlrCopy!$B:$B)-1),6) and this works fine but I would rather define the range using code

How do do this using code?
I was going to use the worksheetfunction but "Offset" does not seem to be an option

This I think is an option but something is wrong with the syntax :doh:

Dim rCopy As Range

With Sheets("BlrCopy")
rCopy = Range("A1").Select.Selection.End(xlToRight).End(xlDown).Select
End With

mdmackillop
03-24-2007, 04:39 AM
Using your methodology
Dim rCopy As Range
With Sheets("BlrCopy")
Set rCopy = Range(.Cells(1, 1), .Cells(1, 1).End(xlToRight).End(xlDown))
End With
rCopy.Select

Bob Phillips
03-24-2007, 07:20 AM
Isn't that CurrentRegion?



Dim rCopy As Range

Set rCopy = Worksheets("BlrCopy").Range("A1").CurrentRegion
rCopy.Select

mdmackillop
03-24-2007, 07:51 AM
CurrentRegion will include columns C, D etc, if there are any cells adjoining column B

Bob Phillips
03-24-2007, 09:25 AM
So will xlToRight!

mdmackillop
03-24-2007, 09:29 AM
Only if C1 is used!

Bob Phillips
03-24-2007, 09:34 AM
I think he started at A1.

mdmackillop
03-24-2007, 09:49 AM
I meant in this fashion.

lifeson
03-24-2007, 10:06 AM
Thanks guys ( I am guessing you are guys :dunno )
Both solutions are workable and select a range but I am getting an error when I try and use the range as the rowsource for a listbox on a user form

Private Sub CheckQty()
Dim dCount As Double
Dim rCopy As Range

Sheets("Copy").Select
dCount = WorksheetFunction.CountA(Range("A:A")) - 1

With Sheets("Copy")
Set rCopy = Range(.Cells(1, 1), .Cells(1, 1).End(xlToRight).End(xlDown))
End With

If dCount >= 1 Then
lstResults.Rowsource = rCopy 'this line is highlighted when I run the code
TextBox1.Value = dCount & " Items match the criteria"
Else
msg = "No items match the criteria!"
ans = MsgBox(msg, vbInformation)

TextBox1.Value = dCount & " items match the criteria, try alternative inputs"
End If


End Sub

FYI I have code that filters records from a data sheet and copies the filtered results to the sheet "Copy" those results are shown on a user form in a multi column list box.

Bob Phillips
03-24-2007, 10:18 AM
Try lstResults.Rowsource = rCopy.Address

lifeson
03-24-2007, 10:26 AM
Try lstResults.Rowsource = rCopy.Address

That works a treat :thumb


I am never going to get the hang of this :cry:

mdmackillop
03-24-2007, 10:34 AM
I am never going to get the hang of this :cry:
Looks to me like you're well on the way!:friends:

Bob Phillips
03-24-2007, 11:07 AM
That works a treat :thumb


I am never going to get the hang of this :cry: You need to learn the art of looking up properties. Both help and the object browser will tell you that RowSource is a string property, so you cannot set it to a range object, you have to set it to that property of a range that defines the cells covered, and is a string, that is Address.