How do I get a cell value to be uses within a macro
 

I am using Excel 2007.
I have macro designed to hide rows which reads:-

Range("A2").Select
Rows("2:1311").Select
Selection.EntireRow.Hidden = True
Range("A2"). Select

This, as expected, hides row 2 to 1311.

I want to replace 1311 with a variable value read from a cell, Z1.

How do I get that cell value to be read in the macro.

I have tried:-

Range("A2").Select
Rows("2:=CELL("CONTENTS",Z1")
Range("A2").Select

but this does not work.

I obviously have a syntax problem, can anyone help please.
--
Mac Macdonald

How do I get a cell value to be uses within a macro
 

hi
try something like this, using variables.....

Dim r As Range
Dim v As Long
Set r = Range("Z1")
v = r.Value
Rows("2:" & v).Hidden = True

works in 2003, should work in 2007. (hope, hope)

regards
FSt1

"Mac0001UK" wrote:

I am using Excel 2007.
I have macro designed to hide rows which reads:-

Range("A2").Select
Rows("2:1311").Select
Selection.EntireRow.Hidden = True
Range("A2"). Select

This, as expected, hides row 2 to 1311.

I want to replace 1311 with a variable value read from a cell, Z1.

How do I get that cell value to be read in the macro.

I have tried:-

Range("A2").Select
Rows("2:=CELL("CONTENTS",Z1")
Range("A2").Select

but this does not work.

I obviously have a syntax problem, can anyone help please.
--
Mac Macdonald

How do I get a cell value to be uses within a macro
 

Add these two lines BEFORE the first line of your code below;
Dim i
i = ActiveSheet.Cells(1, 26).Value

then replace Rows("2:1311").Select with
Rows("2:" & i).Select

Cell Z1 should have the no of rows you want to hide...

"Mac0001UK" wrote:

I am using Excel 2007.
I have macro designed to hide rows which reads:-

Range("A2").Select
Rows("2:1311").Select
Selection.EntireRow.Hidden = True
Range("A2"). Select

This, as expected, hides row 2 to 1311.

I want to replace 1311 with a variable value read from a cell, Z1.

How do I get that cell value to be read in the macro.

I have tried:-

Range("A2").Select
Rows("2:=CELL("CONTENTS",Z1")
Range("A2").Select

but this does not work.

I obviously have a syntax problem, can anyone help please.
--
Mac Macdonald

How do I get a cell value to be uses within a macro
 

You could replace your current code with:

rows("2:1311").hidden = true

(I'm not sure why you'd want to select a hidden cell (A2).)

I'd use something like:

dim myVal as variant
with activesheet
myval = .range("z1").value
if isnumeric(myval) = false then
msgbox "Z1 isn't a number"
else
myval = clng(myval)
if myval < 2 _
or myval .rows.count then
msgbox "invalid number in Z1"
else
.rows("2:" & myval).hidden = true
end if
end if
end with

Mac0001UK wrote:

I am using Excel 2007.
I have macro designed to hide rows which reads:-

Range("A2").Select
Rows("2:1311").Select
Selection.EntireRow.Hidden = True
Range("A2"). Select

This, as expected, hides row 2 to 1311.

I want to replace 1311 with a variable value read from a cell, Z1.

How do I get that cell value to be read in the macro.

I have tried:-

Range("A2").Select
Rows("2:=CELL("CONTENTS",Z1")
Range("A2").Select

but this does not work.

I obviously have a syntax problem, can anyone help please.
--
Mac Macdonald

--

Dave Peterson