Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Paste A Cell Formula Into A Cell With A Macro. | Excel Worksheet Functions | |||
macro to move part of cell contents to another cell | Excel Discussion (Misc queries) | |||
using a cell value to control a counter inside a macro and displaying macro value | Excel Worksheet Functions | |||
Using macro to copy a part of a cell content to next cell | Excel Discussion (Misc queries) | |||
Macro for cell selection starting with Last Cell | Excel Worksheet Functions |