Selecting multiple rows within a CurrentRegion

[Guest]

Greetings:

I'm working on a scheduling application to automate sending appointments to
another user's calendar. I've been successful in limiting the region to just
those cells that are used, and I'm stuck in trying to select multiple ranges
to grab eventual "subjects" and dates for the e-mails. Here's what I have so
far:
Sub Email()
Dim rng As Range, curCell As Range
Dim bytRowNum as Byte, bytColNum as Byte, bytColumnCounter as Byte,
bytRowCounter as Byte

Worksheets("Assign").Activate
Set rng = ActiveCell.CurrentRegion ' Limits area on worksheet that's been used
bytRowNum = rng.Rows.Count ' Counts the rows actually used
bytColNum = rng.Columns.Count ' Counts the columns actually used

For bytColCounter = 4 To bytColNum
For bytRowCounter = 2 to bytRowNum
Set curCell = ActiveSheet.Cells(bytCounter,4)
If curCell.Value = lblEventID.Caption Or Replace(curCell.Value, "@",
"") = lblID.Caption Then
'Here I want to get to the column number of the end cell, which will differ
in situations and grab the date that's in the end cell
End If
Next
Next

Of course, I've tried various methods, but a jumpstart would be appreciated.

Thanks,

 

[Jim Cone]

There are some corrections that need to be made before you can proceed...
Change your variable data types to Longs from Bytes
Add "Option Explicit" as the first line in your module
Correct the misspelled variable names
Then maybe this can get you jumpstarted...
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

Sub Email()
Dim rng As Range, curCell As Range
Dim bytRowNum As Long, bytColNum As Long, bytColCounter As Long, bytRowCounter As Long
Dim dteValue As Date

Worksheets("Assign").Activate
Set rng = ActiveCell.CurrentRegion ' Limits area on worksheet that's been used
bytRowNum = rng.Rows.Count ' Counts the rows actually used
bytColNum = rng.Columns.Count ' Counts the columns actually used

For bytColCounter = 4 To bytColNum
For bytRowCounter = 2 To bytRowNum
Set curCell = ActiveSheet.Cells(bytRowCounter, 4)
If curCell.Value = lblEventID.Caption Or _
Replace(curCell.Value, "@", "") = lblID.Caption Then
dteValue = curCell.End(xlToRight).Value2
End If
Next
Next
End Sub
'-------------------------------


"Brian J. Matuschak"
<[email protected]>
wrote in message
Greetings:
I'm working on a scheduling application to automate sending appointments to
another user's calendar. I've been successful in limiting the region to just
those cells that are used, and I'm stuck in trying to select multiple ranges
to grab eventual "subjects" and dates for the e-mails. Here's what I have so
far:
Sub Email()
Dim rng As Range, curCell As Range
Dim bytRowNum as Byte, bytColNum as Byte, bytColumnCounter as Byte,
bytRowCounter as Byte

Worksheets("Assign").Activate
Set rng = ActiveCell.CurrentRegion ' Limits area on worksheet that's been used
bytRowNum = rng.Rows.Count ' Counts the rows actually used
bytColNum = rng.Columns.Count ' Counts the columns actually used

For bytColCounter = 4 To bytColNum
For bytRowCounter = 2 to bytRowNum
Set curCell = ActiveSheet.Cells(bytCounter,4)
If curCell.Value = lblEventID.Caption Or Replace(curCell.Value, "@",
"") = lblID.Caption Then
'Here I want to get to the column number of the end cell, which will differ
in situations and grab the date that's in the end cell
End If
Next
Next

Of course, I've tried various methods, but a jumpstart would be appreciated.
Thanks,
Brian J. Matuschak
(e-mail address removed)