General discussion

Locked

Excel+ VBA - Selecting a range

By naio ·
How do i refer to a range of cells (row..or column)whose location on the screen (row) and size, varies depending on other variables.

i wish to use this range within a sub procedure....but i always need to refer to that specific data...

This conversation is currently closed to new comments.

7 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Excel+ VBA - Selecting a range

by DKlippert In reply to Excel+ VBA - Selecting a ...

I don't know what kind of data you're using, or if you are using input variables or not.
As a start, take a look at this discussion of dynamic ranges. Let me know if you need something else.

http://makeashorterlink.com/?S28A12D21

Collapse -

Excel+ VBA - Selecting a range

by naio In reply to Excel+ VBA - Selecting a ...

The question was auto-closed by TechRepublic

Collapse -

Excel+ VBA - Selecting a range

by jmiller1 In reply to Excel+ VBA - Selecting a ...

Public Sub Select_Range()
Dim a As String
Dim b As String
a = "A1"
b = "B8"
Sheet1.Range(a, b).Select
End Sub

Collapse -

Excel+ VBA - Selecting a range

by naio In reply to Excel+ VBA - Selecting a ...

The question was auto-closed by TechRepublic

Collapse -

Excel+ VBA - Selecting a range

by srobertson In reply to Excel+ VBA - Selecting a ...

I use this type of thing a lot when I know I have a static start point and need to find the end of a list and complete some actions with it. Below is a sample of code that I use to create a named range for later use. Essentially it go's to the know static cell, tests for data in the cell below and then steps to the last row in the series. To work properly there can be no blank rows.

Sub Set_Transaction_Range()

ThisWorkbook.Sheets("Transaction List").Activate
Range("A4").Select
If ActiveCell.Offset(1, 0).Value <> "" Then
Selection.End(xlDown).Select
Else
ActiveCell.Offset(1, 0).Select
End If

ActiveWorkbook.Names.Add Name:="Transaction_Data", _
RefersTo:="='Transaction List'!$A$3:$F$" & ActiveCell.Row

End Sub

Make sure you include the $ sign when setting the name otherwise the range has tendency to 'move', caused me no end of grief until I worked out what was happening. Naming the range means you can refer back to it later without having to repeat the process.

Hope this helps,

Scott.

Collapse -

Excel+ VBA - Selecting a range

by naio In reply to Excel+ VBA - Selecting a ...

The question was auto-closed by TechRepublic

Collapse -

Excel+ VBA - Selecting a range

by naio In reply to Excel+ VBA - Selecting a ...

This question was auto closed due to inactivity

Back to Web Development Forum
7 total posts (Page 1 of 1)  

Related Discussions

Related Forums