Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


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
  • 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: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

572 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