Select Range (VBA) with Numeric Variables

[Dennis]

Using Excel 2003

Need help using my variables below to select a myRange
that represents one Row by "UsedRange" columns wide.

I want to insert the Range in the loop below but I do not
know how to Code the information into Range.Select
compatible formula.

I did not include the "Dim" statements below.

Sub Replace()
myRowNumber = ActiveCell.Row
myColumnNumber = ActiveCell.Column
myLastColumn = ActiveSheet.UsedRange.Column - 1 +
ActiveSheet.UsedRange.Columns.Count


For myColumnNumber = 1 To myLastColumn
On Error Resume Next
' Set myRange = Intersect(ActiveSheet.UsedRange, Columns
(myColumnNumber), Cells.SpecialCells(xlCellTypeBlanks))
'
' NEED CODE FOR THE ABOVE THAT WOULD WORK WITH MY
VARIABLES
'
For Each myCell In myRange
If myCell.Offset(-1, 0).HasFormula Then
myCell.Formula = myCell.Offset(-1, 0).Formula
Else
myCell.Formula = myCell.Offset(-1, 0).Value
End If

Next myCell

Next myColumnNumber

End Sub

TIA Dennis

 

[Frank Kabel]

Hi Dennis
not quite sure what you're trying to achieve but you may use
Cells(row_number,myColumnNumber)
as range object within your loop. e.g.
Cells(row_number,myColumnNumber).Value = "new value"

 

[Dennis]

Frank,

Thanks for your knowledge & time.

I am trying to get a good value for myRange to insert in
the loop.

It needs to be a Range one row and "X" columns wide.

With that range, the loop will replace values.

The info you sent seems to be a Cell location not a multi-
cell selection that I need.

Maybe I am not expressing my needs well?

TIA Dennis

 

[Bob Phillips]

Dennis,

This does what I think you are trying to do

Set myrange = Intersect(ActiveSheet.UsedRange,
Columns(myColumnNumber).SpecialCells(xlCellTypeBlanks))

But ... as you are using UsedRange, there seems little point in using
intersect, as the column with any data must be part of UsedRange, so this
works just as well

Set myrange = Columns(myColumnNumber).SpecialCells(xlCellTypeBlanks)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Frank Kabel]

Hi Dennis
instead of using myrange you could use the following:
For row_index = 1 to row_count
cells(row_index,"A").value = "some value"
next

this will get the same result as
set myrange = Range(cells(1,"A"),cells(row_count,"A"))
for each c in myrange
c.value = "some value"
next

 

[Dennis]

Bob,

Thanks

I have tried to substitute the various suggestions without
success. I am sure due to something that I am not
perceiving well.

Bottom line:

I need a variable representing a "Range" of cells.

The range should be the active-cell Row; the columns
should be all used columns.

Therefore, i.e., (active-cell) Row 230 (by) Columns A
through Z represents myRange variable.

How do I get VBA to "sense" the last used-column;
Couple that last column range with my selected row;
and enter that information into an "intersect" function or
whatever.

Set myRange = Intersect(ActiveSheet.UsedRange, Columns
(XXXXX).SpecialCells(xlCellTypeBlanks))

How do I get Column A through Z (used Range) into "XXXXX"?
AND only one row of data?

I want a variable for RowNumber not hardcoded "R230"

 

[Bob Phillips]

Dennis,

This code slecet the activecell.row with all columns of the used range

Set myRange = Intersect(ActiveSheet.UsedRange, Rows(Activecell.Row))

this does the same but uses the varianble

Set myRange = Intersect(ActiveSheet.UsedRange, Rows(myRowNumber))

This calculates the last column of that row

Set myRange = Range(Cells(ActiveCell.Row, 1), Cells(ActiveCell.Row,
Columns.Count).End(xlToLeft))

or

Set myRange = Range(Cells(myRowNumber, 1), Cells(myRowNumber,
Columns.Count).End(xlToLeft))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Dennis]

Bingo!!

Thanks Bob

-----Original Message-----
Dennis,

This code slecet the activecell.row with all columns of the used range

Set myRange = Intersect(ActiveSheet.UsedRange, Rows (Activecell.Row))

this does the same but uses the varianble

Set myRange = Intersect(ActiveSheet.UsedRange, Rows (myRowNumber))

This calculates the last column of that row

Set myRange = Range(Cells(ActiveCell.Row, 1), Cells (ActiveCell.Row,
Columns.Count).End(xlToLeft))

or

Set myRange = Range(Cells(myRowNumber, 1), Cells (myRowNumber,
Columns.Count).End(xlToLeft))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)




.

 

[Bob Phillips]

Well I did cover all bases <vbg>

Bob

Bingo!!

Thanks Bob