Excel - Check cell value and display number of rows

Posted on 2010-01-12
Medium Priority
Last Modified: 2012-06-21
I'm very new to working with VBA for excel.  I would like to check a cell D6 whose max value is 6.
This cell basically says how many...
Then according to that number, I want to hide or show a certain number of rows.
Rows 10-15.

I feel like this should be relatively simple.  I have done some VBA for Access, but never in Excel.
Question by:VTKegan
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
LVL 81

Accepted Solution

zorvek (Kevin Jones) earned 500 total points
ID: 26296435
Add this code to the worksheet code module:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = "$D$6" Then
        Rows(10).Resize(6).Hidden = True
        Rows(10).Resize([D6]).Hidden = False
    End If

End Sub

When you change D6 the rows displayed will automatically adjust.

LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 26296437
To add VBA code to a worksheet or chart code module in an Excel workbook, right-click on the worksheet or chart tab at the bottom of the window and select View Code. Paste the code into the document window that appears. Press ALT+F11 to return to the Excel workbook. To find a worksheet or chart module when already in the VBE, press CTRL+R to open the VBE project explorer. Find the module in which the code will be placed - each worksheet and chart module is pre-assigned a name such as "Sheet1 (Sheet1)" where the name inside the parenthesis is the tab name. Double-click the desired module and paste the code into the document window that appears. Press ALT+F11 to return to the Excel workbook.

LVL 18

Expert Comment

by:Cory Vandenberg
ID: 26296487
Something like this in your macro should do what you want.
Obviously you could store D6 in a Long variable too.


If Range("D6") > 0 Then Rows("10:" & 10 + Range("D6") - 1).Hidden = True

Open in new window

LVL 18

Expert Comment

by:Cory Vandenberg
ID: 26296503
Should have refreshed.  Like the use of the worksheet change event much better.  Was just going simple.

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question