easiest way to parse column and row info?

  • Thread starter Thread starter ker_01
  • Start date Start date
K

ker_01

I'm trying to set up a macro to function on a user-selected range (rather
than hardcoding row/column references, or having a bunch of inputboxes to
collect the reference info)

Getting the range is easy;

Dim TempRange as Range
Set TempRange = Selection
debug.print TempRange.Address(False, False)

which gives me (in my test scenario): B6:AD1463

My macro will need to process each column independently, and for each
column, I will loop through each row and perform some actions.

I can use left/right/mid to pull apart B6:D14 into the component pieces
(B..AD and 6..1463) but the code isn't elegant because I have to manually
determine where the column reference ends and the row number begins. For
example, AF2 vs A63 are the same length strings, so it requires extra logic
to pull them apart correctly.

I'm sure there is a way to directly extract the column and row information
(letting Excel figure out which characters are which), but I'm having trouble
finding the correct syntax and/or applying it to a multicell range.

I appreciate your suggestions,
Keith
 
Lets say we have selected a single block. This little macro will give the
limits of the block:

Sub range_reporter2()
Dim r As Range
Dim s As String
Set r = Selection

nLastRow = r.Rows.Count + r.Row - 1
MsgBox ("last row " & nLastRow)

nLastColumn = r.Columns.Count + r.Column - 1
MsgBox ("last column " & nLastColumn)

nFirstRow = r.Row
MsgBox ("first row " & nFirstRow)

nFirstColumn = r.Column
MsgBox ("first column " & nFirstColumn)
End Sub

You can setup loops to traverse the columns/rows, etc.
 
Excellent, thank you!

Gary''s Student said:
Lets say we have selected a single block. This little macro will give the
limits of the block:

Sub range_reporter2()
Dim r As Range
Dim s As String
Set r = Selection

nLastRow = r.Rows.Count + r.Row - 1
MsgBox ("last row " & nLastRow)

nLastColumn = r.Columns.Count + r.Column - 1
MsgBox ("last column " & nLastColumn)

nFirstRow = r.Row
MsgBox ("first row " & nFirstRow)

nFirstColumn = r.Column
MsgBox ("first column " & nFirstColumn)
End Sub

You can setup loops to traverse the columns/rows, etc.
Click to expand...
 
I'm trying to set up a macro to function on a user-selected range (rather
than hardcoding row/column references, or having a bunch of inputboxes to
collect the reference info)

Getting the range is easy;

Dim TempRange as Range
Set TempRange = Selection
debug.print TempRange.Address(False, False)

which gives me (in my test scenario): B6:AD1463

My macro will need to process each column independently, and for each
column, I will loop through each row and perform some actions.

I can use left/right/mid to pull apart B6:D14 into the component pieces
(B..AD and 6..1463) but the code isn't elegant because I have to manually
determine where the column reference ends and the row number begins. For
example, AF2 vs A63 are the same length strings, so it requires extra logic
to pull them apart correctly.

I'm sure there is a way to directly extract the column and row information
(letting Excel figure out which characters are which), but I'm having trouble
finding the correct syntax and/or applying it to a multicell range.

I appreciate your suggestions,
Keith
Click to expand...

Here is an example macro that you can adapt to your needs:

Sub sum_squares_in_selection()
' if you want the range to be processed column by column, try this
s = 0
For i = 1 To Selection.Rows.Count
For j = 1 To Selection.Columns.Count
' do your stuff here, summing squares is just an example
s = s + Selection(i, j).Value ^ 2
Next j
Next i
MsgBox "Sum of squares is: " & s

' if you don't mind if the range is processed row by row, try this
s = 0
For Each c In Selection
' do your stuff here, summing squares is just an example
s = s + c.Value ^ 2
Next
MsgBox "Sum of squares is: " & s
End Sub

Hope this helps / Lars-Åke
 
Another way...

Dim myRng as range
Dim myArea as Range
dim myRow as range
dim myCell as range

set myrng = Selection

for each myArea in myRng.areas 'in case of multiple areas
for each myrow in myarea.rows
for each mycell in myrow.cells
msgbox mycell.value & vblf & mycell.address
next mycell
next myrow
next myarea

=======
And if you don't want to rely on the selection.

Dim myRng as Range

set myrng = nothing
on error resume next
set myrng = application.inputbox(prompt:="Select a range", type:=8)
on error goto 0

if myrng is nothing then
'user hit cancel
exit sub '???
end if

for each myarea in myrng.areas
...
 
Back
Top