Help - selecting range

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

MJKelly - 19 Mar 2008 20:57 GMT

Hi,

I have a set of data which starts at C22. there is data in the cells
around this, but I am only interested in selecting the range C22 and
all cells containing data to the right and cells containing data below
cell C22. I have used xltoright and xldown before, but can't seem to
get them to work together to select a multi column/row range. The
number of columns and rows of data does change.

hope you can help?

Matt

Reply to this Message

Gary''s Student - 19 Mar 2008 21:56 GMT

Sub across_and_down()
Set r = Range("C22")
For i = 4 To Columns.Count
If IsEmpty(Cells(22, i).Value) Then
Else
Set r = Union(r, Cells(22, i))
End If
Next

For i = 23 To Rows.Count
If IsEmpty(Cells(i, "C").Value) Then
Else
Set r = Union(r, Cells(i, "C"))
End If
Next
r.Select
End Sub

This macro will first move across row 22 starting from C22 and pick up cells
with data. The macro will then move down column C from C22 and pick up cells
with data

Signature

Gary''s Student - gsnu2007g

> Hi,
>
[quoted text clipped - 8 lines]
>
> Matt

Reply to this Message

Rick Rothstein (MVP - VB) - 20 Mar 2008 07:05 GMT

I seem to be reading your request differently than Gary''s Student did. I'm
assuming you have a blank column just to the right of your data and a blank
row just under your data and that you are looking to select all of your data
only (no matter what else lies on the other side of the blank column and
blank row. If that interpretation is correct, this code will make that
selection...

Sub SelectMyData()
With Worksheets("Sheet1").Range("C22")
Range(Cells(.Row, .Column), Cells(.End(xlDown).Row, _
.End(xlToRight).Column)).Select
End With
End Sub

Rick

> Hi,
>
[quoted text clipped - 8 lines]
>
> Matt

Reply to this Message