Hide Rows Beneath Except One For Input

Dec 30, 2006

I have two columns and each record in the spreadsheet fills both cells. I want all rows beneath the data to be hidden except for one, which is for the user to input a new record. When they are done completing the record, 1 row becomes unhidden (which will be for another inputted record). I also need it so that the new row becomes unhidden ONLY when the user inputs values in BOTH columns of the new record. I have already used Data Validation on each individual cell (column a is validated only for numeric entries and column b can only be picked from a drop-down list)

View 9 Replies


Excel 2007 :: Autofilling Value To 4 Rows Beneath Last Value In Column?

Jul 24, 2012

I am still new to this, and I pulled this code off of another site.

- Using Excel VBA, 2007
- I have a sheet that has a name in B6, has 4 blank spaces beneath, and then another name, 4 blank spaces, etc.
- I wrote a code to fill each of these 4 blank spaces with the value at the top of each
- The code works, but it won't fill the 4 spaces at the bottom of the column.

What is needed:
-how I can adjust to code below to fill in the 4 spaces beneath the last value in the column?


Sub FillBlanks()
Dim rRange1 As Range, rRange2 As Range
Dim iReply As Integer


View 3 Replies View Related

Vba Shade A Cell Once Its Moved ( And Add New Rows Beneath ) Minor Code Adjustment

Nov 7, 2009

I've adjusted a jonmo code to add an item in col B which is not in col A to the bottom of col A. - fab code, thanks jonmo.

But.. i want to:

insert rows beneath those in column A to accommodate the added items and shade those cells in list A once they been added ( so the users now they've been moved )

I've posted the code below ( including my attempts at colour change where it shade the right cell but in the wrong column ) ...

View 9 Replies View Related

Hide Password Input

Jul 12, 2007

like PHP, you can enter a box and have it so what is entered is meant to be a password, so puts stars where the text is. I would like to do something like that with VB INPUT tag:

pw = Input ("Please enter Password:")

Is there a method I can add to this to make it cover the text being typed?

View 9 Replies View Related

Hide Specific Rows In Sheet1 And Hide 5 More In Sheet2?

May 26, 2014

I have a macro in which i can enter the rows i want to hide.

If i want to hide "position 32" i have to enter the number 8 of the row. This works fine. But now if i want to hide the "position 32" from Sheet1 it also should hide the rows 4-8 from Sheet2 [Data with 32].

Or if i hide "position 34" in Sheet1 [row 10] it also should hide the rows 14-18 in Sheet2.

View 14 Replies View Related

VBA - Button Command To Auto-Hide Rows With Value HIDE

Jun 9, 2013

Sub Button294_Click()

If Sheet1.Range("A34:A94") = "HIDE" Then
For Each cell In Range("A27:A94")
If UCase(cell.Value) = "HIDE" Then
cell.EntireRow.Hidden = True
End If
End Sub

View 4 Replies View Related

Msgbox And Input Box To Hide Columns

May 29, 2012

I have sheet with Oval button and when I pressed the Oval button input box appears and ask for password and I made msgbox if the password is wrong and I made loop 4 times to enter the password then after for trials will exit the inputbox ,But I have 2 problem with my code below

1st problem : when i press the cancel button before I insert any character in Inputbox the msgbox appears which its say the password is wrong

2nd problem : when i press the cancel button before I insert any character in Inputbox the column which the Oval Button exist on it disappear.


Sub Hide_Sal_Table()
Const pass As String = "123"
Dim Inp
Dim Retry
Dim lTries As Long
Inp = InputBox("please enter the password", "Zmhnk", vbOKCancel + vbQuestion)
Application.ScreenUpdating = False

[Code] ........

View 1 Replies View Related

Dropdown List With Prompt Box To Enter Value And Hide Input

Feb 5, 2013

I am looking to allow a user to enter a unique value when a drop down value is captured from a list i.e 'Other' (Possibly a pop up box), then I require this value to be hidden and calculated along with others to show the average (The average calculation obviously wouldn't be in the same cell).

View 8 Replies View Related

How To Hide Tabs And Make Them Appear After Specific User Input

Jul 10, 2014

I have nearly finished my project, but need to make the last real bit of code, then just some general tidying up.

What I want to do
- I currently have two tabs in a userform - one called 'main' and one called 'extra'.
- I want Main to always be shown to the user.
- I want Extra to be hidden (is it possible to make the tabs invisible?) and for this form to be shown only under a specific condition

I have a question on my userform (using Option Buttons):
"Did the customer ask about an extra product today?"

If the answer to this question is no - When the user hits the command button (after filling out the rest of the form), do nothing special - just return all the values to the worksheet

If the answer is yes - When the user hits the command button, I want them to be taken to the 'extra' tab. They will fill out some additional check-boxes, then hit another command box, which will return the info from both the 'main' and 'extra' sheets into the same row of the worksheet.

In brief: I want my 'extra' tab to be hidden, only to appear when 'yes' is selected, and for the results all to go back to the worksheet.

View 6 Replies View Related

Hide And Delete Sheets Based On Cell Input?

Aug 11, 2014

I have a situation where i would like to hide and delete a few sheets in my worksheet.

there is a few sheets where i want it to be constantly visible and one sheet is based on a cell reference.

the remaining irrelevant sheets i would like it to be hidden and deleted.

'always visible are sheets that are constantly visible in all files' 'visible msds is the sheet where my product data is being pulled out based on a input i enter on my first sheet' the sheets i want to hide and delete will be the remaining irrelevant "MSDS" that i would like to remove through macro.

View 3 Replies View Related

VBA To Hide Columns Based On Input Date Range?

Mar 26, 2014

I need some VBA code to hide columns if they are outside of a specified date range.

- the worksheet i need to run this VBA on is named 'Summary'
- Columns A to G need to remain un-hidden at all times
- from H17 to ZZ17 i have every month of the relevant years listed (all there chronologically)
- D3 holds the 'Date From' variable
- D4 holds the 'Date To' variable

View 11 Replies View Related

Bring Values Beneath Each Other...

Sep 17, 2009

I have a sheet made of 3 columns, every column has several rows, I am trying to bring the values of the second column beneath the values of the first column and then the values of the third column beneath them all. Attached are 2 sheets showing the current and required scenarios

View 4 Replies View Related

Insert Row Beneath And Copy Formula?

Dec 16, 2011

I'm trying to create a fairly dynamic report in excel based on queries imported from access into a excel template (the queries are imported into separate worksheets). So what I want is simply use a =Count to find the number of rows in my imported query and based that count value insert an additional row beneath the first line of the report worksheet and copy down the formula.

View 2 Replies View Related

How To Select Cell Beneath Chart

Jan 2, 2012

I have a chart, I need to select a cell beneath that chart. It can be the top cell or top left cell. So is there a way, by using vba, to know which cell is the top or left cell being covered by a chart

View 2 Replies View Related

Find Specified Text In Column And Sum Values Beneath It?

Jun 4, 2013

I have some xls files. I need to find the sum of a specific column. the column heading is always TOTAL but the column it resides in changes column to column. so one file may have the text total in column H and others will have the text total in column X. how can i make a macro so it finds the text 'total' and then sums up all the values in that column underneath it and displays it at the very bottom of that column?

View 9 Replies View Related

Macro- Find Cell, Select That Row, And The One Beneath It

Feb 19, 2008

When recording a macro, how would I have it record me searching for a word in a cell, then select the two rows beneath it so that I can hide/unhide the hidden rows?

The reason it has to search for the word in the cell is that with changes above, the rows keep getting bumped further and further down (i.e., a moving target).

View 9 Replies View Related

Copy Named Range To Row Beneath Last Entry In Another

Feb 15, 2009

I have a number of named ranges on seperate sheets. As I add more entries to each sheet, the named range is naturally increasing so I dont have a fixed cell refence
Effectively what I would like to know is - is there coding that would allow me to copy one named range - and paste the values to the the row beneath the last entry in a second named range. Filtering these I can do - it is just the copying that has me stumped

View 3 Replies View Related

Counting The Number Of Non-empty Cells Beneath A Reference Cell

Aug 18, 2009

I'm trying to count the number of non-empty cells beneath a reference cell and would be most grateful for a few pointers. The code I currently have (that doesn't work) is:

View 2 Replies View Related

Hide Blank Rows Based On Values In Other Rows

Nov 3, 2006

I have a worksheet used for inventory. In Column A is the quantity (to be entered manually). In Column B is the product description. In Column C is the price of the product, and Column D the total price (column C price x the quantity entered in Column A). At the bottom of the worksheet is a grand total. Also, Column B (products) is grouped into subheadings by the supplier each product came from (for example, row 6 has the title PPG, and then rows 7-137 list every product from PPG).

The calculations in this worksheet work fine. What I am trying to do is, using a macro once all of the appropriate quantities are entered in column A, automatically hide every row of product that does not have a quantity. The tricky part is, if no products under a given supplier subheader are entered, the subheader also hides, and if a quantity is entered, that subheader shows. For example, if I have no quantities under any products for PPG, then the PPG subheader hides, but if just one quantity is added, PPG shows. Also, this list will be constantly updated, new products will be put in and taken out all of the time, so I cannot base the macro on a specific number of rows.

View 2 Replies View Related

Delete Certain Rows With An Input Box

Jul 4, 2009

I would like a macro that does this. Note for below that the "state" column will never be in a fixed column position, so this macro must search for "state" by name and not column position.

1. Find the column named "state"
2. Bring up a message box saying "Please enter the state/states you would like to use, separate each state with a comma"
3. The user would then enter in each state/states they want to use and press "OK"
4. The macro then deletes ALL ENTIRE ROWS NOT matching the state/states IN THAT COLUMN that were entered in the message box by the user (except the header row of course). To clarify number 4 further, the macro should ONLY look in the "state" column when deciding whether to delete the WHOLE ROW or not.

5. The case should be non case-sensitive, for the "active worksheet", and if possible, should work whether the user enters in more states with either

a) comma state


b) comma space state

6. Also, I'm only using "states" for example reasons only but actual text length can more more characters long. The criteria entered in the message box should not be limited to two characters.

View 2 Replies View Related

Log User Input In Successive Rows?

Feb 4, 2014

I am trying to build a user-friendly Excel database that takes the user's input from various cells and logs that data input by the user in successive rows to the side. Please see the screenshots of my current spreadsheet below:

Compressor Database.jpg

From the screenshot above, I would like for the user to input their data for the Date of Last Inspection, Current Hours, and check the boxes for any services they performed in the orange/pink "Input" box on the left. Currently I have the spreadsheet set up so that the orange/pink "input" areas recognize data put in to the yellow "Data Log" cells to the right. I am trying to get the yellow "Data Log" cell area to store the Date and Hours data put in by the user in successive rows like a database, but I am not sure how to do this. I was thinking of putting a button in the says "Log Data" which would, when clicked, store the data in the "Data Log" cells. I am unsure if I need to use a Macro or whether Excel has a simple, built-in function that can be used. Eventually I have plans for the "Date of Last _______ Change" Gray boxes, but would like to take this one step at a time.

I am not well versed in VBA and Excel Macros, and I am currently using Microsoft Excel 2007 (yes, I know it is old). I do have C# and Matlab coding experience, however I would like to do this in Excel to make it easier for the User as it is a well known program.

View 14 Replies View Related

Moving Rows Based On Input Of Date?

Jun 17, 2014

I am a production manager for a homebuilder and I am trying to create an Excel spreadsheet that will allow me to input a date in a column and have a second column dependant on the date and move as I change the date.

Since no work is done on weekends, I'd like the output column to skip weekends and move the input to the next available weekday. I have conditionally formatted the date to be a red fill as a visual cue but I'm not sure where to go from here.

View 7 Replies View Related

Delete Rows Based On User Input

Mar 6, 2009

1) Allow user input of a number between 1 and 999

2) Search column three and delete any rows that don't match this number.

I have some code that would delete any rows within a certain column, based on predefined criteria and i've tried modifying it to suit my needs.

View 7 Replies View Related

Show Rows Based On Value Entered In Input Box?

Mar 7, 2014

I would like to present the user with an input box asking him to enter a numerical value greater than 0.

Then I would like the code starting with row 10 and consult the vaues in column O, and only display the rows that have number in it less than or equal to the number in the input box. I was hoping it would involve a filter.

I would also need a macro to display all of the rows again.

If this is too hard, how about copying the results to a new worksheet along with the labels in row 9?

View 2 Replies View Related

Hide Rows With No Value?

Jul 17, 2014

I have a worksheet that has a quantity column "A" and if there is no value in it I would like to hide all rows without values so only rows with quantities remain. Based on the code I have supplied below you will notice I have ranges of cells that I want to work with. The code I have works well with the exception it takes a long time maybe 30-45 seconds to perform the operation. I was wondering if there is a faster way to perform the function. I have saved it as a macro which I tied to a button.

[Code] .....

View 9 Replies View Related

Hide Rows With VBA

Oct 6, 2008

I have sheet 1 and 2 in a work book. Sheet 1 is a list of data consisting of names and address, and quantity, type, price, etc of items shipped to them. Sheet 2 takes the data from sheet one and computes certain formulas. I have allotted up to 100 rows of data to automatically compute, however I rarely need that much. Basically it goes like this. When I open the spreadsheet sheet 1 is not actually blank, instead in cell A1 I have the phrase "Customer Name", in cell B1 I have the phrase "Account Number" and so one. I do this so my coworkers know which info must go in which cells. As long as the info is put in the correct spot than cell A1 in sheet 2 with do X and B1 will do Y and so on. so if I put in 10 customers with accompanying data then the first 10 rows of sheet 2 will compute formulas and the other 90 rows will simple state "No Data" in the cells. What I would like to do is create a macro or assign a command button to HIDE all the rows that say "No Data" in column A. So if I have 30 customers entered then when I go to Sheet 2 all I have to do is hit a button and rows 31-100 become HIDDEN, not deleted.

View 3 Replies View Related

How To Hide All Rows

Feb 22, 2013

I preferrably want to hide all rows from row 58 and down, but then unhide some rows. The rows that I want to unhide is from row 1000. How many rows that I want to unhide, depends on the content of that list. If the list is empty, then I want to unhide rows 1000-1003.

What I have written (but doesn't work), is:


Selection.EntireRow.Hidden = True
If Range("C1002") = Empty Then
Erstatningsgrunnlag = Range("B1000:AG1003")
Else: Erstatningsgrunnlag = Range("C1001:AG") & Range("C1001").End(xlDown).Row + 1
End If
Erstatningsgrunnlag.EntireRow.Hidden = False

View 3 Replies View Related

Hide Rows If Value Zero

Oct 29, 2013

Rows 9-79 - if value in column D is zero, then hide row.

How do I do this?

Also - can this be triggered by just selecting the worksheet, or will I have to use a button?

View 4 Replies View Related

Hide All Rows With ZERO Value

Aug 21, 2007

Is there such a way/function that i could simple hide/exclude any row with ZERO value in either a pivot table or in a regalur table of data? As of now, i have to manually find the row with zero value and hide them individually.

View 9 Replies View Related

How To Hide Rows

Jan 8, 2008

I have a worksheet "ULIP21.xls", where in cell C10, the value can be either "Yes" or "No". If the value is "No", I want the rows 31 to rows 82 hidden in the sheet "INPUT" of the worksheet.

View 9 Replies View Related

Copyrights 2005-15 www.BigResource.com, All rights reserved