Hides And Unhides Certain Rows In A Worksheet

Feb 21, 2008

I have a macro that hides and unhides certain rows in a worksheet based on what number (1-10) is entered in a cell on that same worksheet. I have this same macro in different worksheets hiding/unhiding different rows based on this same number. So here's the problem: right now I have a number entry cell on every worksheet. I would like to only have it entered on 1 worksheet, with the rest of the macros reading the cell from that 1 worksheet.

View 11 Replies


ADVERTISEMENT

Unhides Rows/columns When Formula Is Entered

May 1, 2008

One of my co-workers is using excel 2000 and when she enters a formula into any cell, adjecent to a hidden row/column or not, it automaticaly unhides all hidden rows & columns. My PC does not do this. I tried to find a setting to duplicate this and have not found anything yet.

View 9 Replies View Related

Yes Or No Question Hides Rows

Mar 19, 2012

I am completely new to using Macros in Excel. I have a cell with a yes or no question. If the answer to the question is Yes then I want rows 30-42 to be displayed. If the answer to the question is no I want rows 30-42 to disappear.

I have tried several different ways of doing this and no matter which one I use I get the same error. It says, "Argument not optional".

Here are the two different ways I'm trying to do accomplish my goal right now.

Attempt 1:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$BS$15" Then
Select Case Target.Value
Case ""
Rows("30:42").Hidden = False

[Code] ........

Attempt 2:

Private Sub Worksheet_Change(ByVal Target As Range)
For Each Cell In Range("$BS$15")
Select Case Cell.Value
Case vbNullString
Rows("30:42").EntireRow.Hidden = True

Code] ..........

View 9 Replies View Related

Macro That Hides Rows

Sep 7, 2008

I have a sheet that has the ability to display information for 60 employees, but most of the time there is only 25 - 30 employee row being used.

The sheets starts are row 1 and goes to row 60. A61 has a formula that counts how many employees there actually are and where the last employee is. (example: if A61=32 than I need to hide rows 32 to row 60)

View 9 Replies View Related

Hides Rows If Cell In Column Empty

Jan 10, 2007

I currently have a worksheet with a range of A1:P2500. I am trying to create a macro that will check every Row to determine if Column A is blank (""). If Column A is blank in this row, It will hide this row and then continue to the next, until all 2500 rows have been accounted for.

View 4 Replies View Related

Loop Through A List And Hides Rows That Doesn't Contain Value Of Textbox

Nov 26, 2011

I have the below code that loop through a list and hides rows that doesn't contain a value of the Textbox, it works fine but becomes very show where number of records it passed through the loop is greater than 1000, is there more efficient way of writing this code?

Number of cells to loop through is 10000 Max

PHP Code:

Private Sub tboxSearch_Change() 
LastRow = Cells(rows.count,5).end(xlup).row 
For Each cell In Range(Cells(55, 5), Cells(LastRow,5)) 
If InStr(UCase(cell.Value), UCase(tboxSearch.Value)) = 0 Then 
Rows(cell.Row).EntireRow.Hidden = True 
End If 
Next cell 
End Sub 

View 8 Replies View Related

Macro Identifies FULL ORDERS And Hides All Other Rows

Jul 25, 2007

macro - show rows ONLY if two columns = each other AND......
Hello Excel Swammis!

I am in need of your assistance again.

I have an Excel report set up as follows:

Col D - Order #'s
Col G - part #'s
Col H - warehouse code for each part # ("N", "M", etc...)
Col K - Qty of part # ordered
Col L - Qty reserved for that part # on the order

List of orders starts on row 9.

If a particular line on the order is filled, then Col K = Col L for that row.
When the whole order is filled, then Col K = Col L for all rows on that order.

What I need is a macro that identifies FULL ORDERS and hides all other rows. So, if Col K = Col L for all rows adjacent to same order #'s in Col D (AND if Col H has value of "N" or "M" for warehouse code), it will be visible. Any orders with even ONE unfilled line will be hidden.

I am also looking for the reverse of this for a seperate macro. IE: Any orders with even ONE line not completely filled, I want all rows for those orders to be visible and all else hidden (again, only if Col H = "N" or "M").

View 9 Replies View Related

Unchecking A Checkbox Hides A Sheet

Oct 3, 2007

Code that will make it so that when a checkbox is UNCHECKED, the sheet that it originally opened re-hides itself? In other words, I have a mcor that works great that allows for a checkbox when checked to unhide a worksheet and take the user to that sheet. Now I need it si that when it is UNCHECKED, the sheet goes back to it's hidden state. This is what I currently have that UNHIDES it. Obviously somehow I need it to REHIDE it after being unchecked:

How would the corrected code look AFTER being added to this one:

Sub CheckBox615_Click()
Sheets("FedEx Freight Opp Form").Visible = True
Sheets("FedEx Freight Opp Form").Select
Range("B16").Select
End Sub

View 9 Replies View Related

Code Hides The Arrow In Column

Mar 11, 2009

I have a sheet that is autofiltered. Once a user autofilters columns 20, I would like to hide the arrow for that column so the user can no longer autofilter using that column. When I run the code - it hides the arrow in column 20, but then it unfilters all the data . . . can I hide the arrow in column 20 but keep the data as it was filtered before the code ran.

Dim c As Range
Dim i As Integer
i = Cells(2, 1).End(xlToRight).Column
Application.ScreenUpdating = False

For Each c In Range(Cells(2, 1), Cells(1, i))
If c.Column = 20 Then
c.AutoFilter Field:=c.Column, _
Visibledropdown:=False
End If
Next
End Sub

View 9 Replies View Related

Macro Hides Userform Without A Reason

Mar 25, 2007

to ensure the user ends up looking at UserForm5 after having closed the UserForm2 with the x sign.

It works just great - the user gets logged on and can use the UserForm2 but when he/she closes the form with the cross and tries to lo on aner using the UserForm5 - which pops up autmatically due to the above code, both of the UserForms get cloed (or hidden).



Private Sub CommandButton1_Click()

Dim popup As Variant

If ExecuteExcel4Macro("'D:[target.xls]Trg Info'!R1C255") _
= UserForm5.TextBox2.Value And ExecuteExcel4Macro("'D:[target.xls]Trg Info'!R1C256") _
= UserForm5.TextBox3.Value Then

With UserForm5

.TextBox2.Value = ""
.TextBox3.Value = ""
.Hide

End With

UserForm2.Show

With UserForm2 .....................

View 9 Replies View Related

Count Rows In Column On Worksheet Then Insert That Many Rows On Another Worksheet

Nov 15, 2013

Count all the true statements in column A (Work) of sheet1 (Checklist), once counted insert that many rows on sheet2 in a specific location, I found a count formula just don't know how to do the insert rows part

Code:
Sub CountRows()
Dim Rng As Range, CountTrue As Long
Set Rng = Sheets("Checklist").Range("Work")
CountTrue = Application.WorksheetFunction.CountIf(Rng, "True")
End Sub

View 3 Replies View Related

Conditionally Hiding / Unhiding Rows In One Worksheet By Referencing Cell In Different Worksheet

May 3, 2013

coding a VBA macro for one of my workbooks, in which I need to be able to hide/unhide various rows in one worksheet depending on the value of a cell in a worksheet elsewhere in the workbook. The rows start off hidden by default.

As a simplified example:

Worksheet1 has a cell that has option "Set 1," and "Set 2." Worksheet2 has two sets of rows (say, 20:30 and 40:50) that need to be hidden/unhidden depending on the cell in Worksheet 1. These are hidden to start with!

So if Worksheet1's target cell says "Set 1", then on Worksheet2, rows 20:30 would stay hidden and rows 40:50 would be revealed, and then if the target cell says "Set 2," then on Worksheet 2, rows 40:50 would then be hidden, but rows 20:30 would then be revealed.

I was thinking of using something like this:

Rows("20:30,40:50").EntireRow.Hidden = True
If Target.Address="'Worksheet1'!A1" Then
If Target.Value = "Set 1" Then
Rows("40:50").EntireRow.Hidden = False
Else
Rows("20:30").EntireRow.Hidden = False
End If
End If

I think this might work, but every time I try to run this I get various errors, like not referencing my target cell correctly.

View 5 Replies View Related

Combining/merging Multiple Rows From One Worksheet Into One Cell In Another Worksheet

Jan 28, 2010

I'm trying to create a macro to merge multiple rows into one cell and display in a new worksheet.

This seems really simple but I've tried to re-work some other examples I've found online but none seem to do exactly what I need. I'm also pretty new to VBA , so it's highly possible i've missed something.

I need to display each set of Notes for each DonorNo in one row - with each note separated by a space.

I've attached a sample of the data and what I need for the output. In the actually file I have around 70,000 records so the prospect manually merging the rows is horrifying.

View 12 Replies View Related

Formatting Worksheet: Re-adjust The Worksheet To Make It More User Friendly Based On Number Of Rows In Current Sheet

Oct 14, 2008

I have a worksheet with 30,000 rows. But sometimes even if I have fewer records in this worksheet(lets say 1000) worksheet shows the same 30,000 rows.And its annoying when you try to navigate using vertical scroll bar. Is there any option to re-adjust the worksheet to make it more user friendly based on number of rows in current sheet.

View 2 Replies View Related

Populate New Worksheet With Preselected Highlighted Rows From Another Worksheet

Nov 26, 2012

How do I populate a second worksheet with only the preselected highlighted rows of cells from another worksheet.

This way I will end up with only the selected data from the first worksheet in the second worksheet.

View 9 Replies View Related

VBA To Copy Selected Rows In One Worksheet To The End Of A Table In Another Worksheet

Jun 22, 2006

I would like to be able to select several non-sequential rows in a worksheet called "Data" (using a check box or just entering a value in Column A) and then be able to press a Command button to copy the selected rows to another worksheet called "Estimate" at the bottom of a table, and delete the designators in Column A (i.e. deletes the value, or unchecks the boxes) so I can repeat the process again if needed.

View 7 Replies View Related

Delete Rows Same As Rows On Another Worksheet

Nov 17, 2007

I have a workbook where I import data from another workbook. This might get done several times a month. As I go through the imported data I delete rows not needed which gets place into another worksheet. What I need is when I import data again is a macro that the worksheet where my already deleted rows are and compairs them to the new data imported and if the first 13 columns of data matches it delest them off the imported sheet.

I found some code which I altered at [url]

and posted the same question but haven't got any response as of yet.

So the code that i go works but only if the row matches in the exact order on both sheets so e.g. (if row 1 on sheet1 matches row 1 on sheet2) it works but if (row1 on sheet1 matches row 3 on sheet2) it does not.

Here's the code I have so far.

Public Sub delOLD()
Dim i As Long
Dim iLastRow As Long
Dim ws As Worksheet, ws1 As Worksheet

Set ws = Sheets("Data") ' Imported Data
Set ws1 = Sheets("Old Records") ' Deleted Data

'ws1.Visible = xlSheetVisible

View 4 Replies View Related

Cut Rows From One Worksheet To Another If Condition Met

Sep 25, 2012

I would like an easy/fast way cut an entire row of data from one worksheet ("Open Work") to another ("Closed Work"). Each worksheet has a matching Header Row from column A - N. In "Open Work" I have column "K" with various values, but I'm looking specifically for the "CLOSED" value. If a cell in column "K" = CLOSED, I want to cut the entire row and paste it into the other worksheet "Closed Work".

That was a no-go! It copied limited rows AND duplicated them.

VB:
Sub copyrows()

Dim tfCol As Range, Cell As Object
Set tfCol = Range("A2:A9") 'Substitute with the range which includes your True/False values
For Each Cell In tfCol

[Code] ......

View 6 Replies View Related

How To ADD ROWS To 2007 Worksheet

Jan 18, 2009

how to add rows which was so simple in earlier Excel versions, but now is bafflegab.

Even after several efforts to comply with what follows when at INSERT, all I got was to move cell I'm in one to the right with all other cells in the row following suite.

OF COURSE I had specified "entire row".

View 13 Replies View Related

Extra Rows In Worksheet?

Feb 26, 2014

I'm using Excel for holding lots of numerical data. Another software program pulls the information from Excel and is used to evaluate the data. The problem I am having is that some of the worksheets in my workbook have extra rows, and by "extra" I mean over 1,000 extra, blank, empty, rows. This means that in the other software program, it pulls over thousands of blank cells that have to be deleted every time it is opened.

View 6 Replies View Related

Copy The Rows From One Worksheet To Another

May 10, 2009

This is a simple macro that copies the rows from one worksheet to another, but how do I get it to copy the values only and not the formulas?

View 2 Replies View Related

Number Of Rows In Worksheet

Jan 23, 2008

I have got an excel worksheet containing about 5000 records. However my excel file shows rows numbered up to 13000. All rows from 5001 onwards are blank.

The problem that i am having is that the scroll bar becomes smaller and it becomes difficult to navigate to a certain point within the sheet.

Is there a way i can delete or limit the number of rows that appear in excel. I have tried to select the rows from 5001 onwards and delte them but it doesnt work and i still have over 13000 rows.

View 9 Replies View Related

Hide Worksheet That Contains Over 15k Rows

Mar 12, 2009

I have a worksheet that contains over 15k rows, each row, in column AK has an input of X-123, x123, 123, ENVEL, ROL, WPL-503, etc. there are a total of 20 different inputs. These inputs are found on another file and inputted from with a Vlookup formula.

I tried advanced filter, but it is limited and thought, if I have a command button that opens a userform then I can select which ones I want to be hidden.

I don't know exactly if I should use a checkbox, listbox, combo box ect.

View 9 Replies View Related

Copy Rows To Another Worksheet

Apr 8, 2009

Post1:

I need a macro that will search column R for a date, and search column E for a value (Signature, Revised Signature or Custom Finals). When those conditions are met, certain cells (C,D,E,F & M) need to copy over to another sheet. Also certain cells in that row have formulas (I,J,Q & U) that need to be copied over. Both sheets have the same column headings, A5-U. Everytime the macro is run I need it to look at column C to see if the project # (ex. 0901234) is the same, I don't want duplicate project numbers in sheet 2.

Post 2:

Sheet 1 is (Assignment List), Sheet 2 is (Cut Sheet). The dates are in this format m/d/yy. The column heading is in the same order. Column R is the completed date in the "Assignment List" sheet. In column E, if it reads Approval and column R has a completed date i don't want it to copy over, but if it reads Signature, Revised Signature or Custom Finals and it has a completed date i want it to copy into the (Cut Sheet) sheet starting at row A6.

View 10 Replies View Related

Copy 1st X Rows To Another Worksheet

Mar 17, 2008

There is a sales sheet which basically produces a sales receipt and a report sheet which should keep track of all the items sold. so i was wondering how a simple do loop or equivalent command could be used to copy about 15 rows (6 cells from each row) from the sales sheet to the report sheet (technically starting from the first available empty row in the report sheet) each time a sales receipt is produced.

View 3 Replies View Related

Extend Worksheet To 1 Million Rows?

May 20, 2009

I have a worksheet that was originally saved as a xls (2003) file. I have resaved it as a xlsx (2007) file, but the max rows are still at 65,536. I am trying to combine over 100,000 records into one sheet. The only way I have been able to create a excel document with more than 65,536 rows is to export my records from an Access file.

View 2 Replies View Related

Insert New Rows Into Protected Worksheet?

Oct 13, 2013

I have created a table where the first 6 columns are data entry and the next 6 columns are formulas to give desired results.

I have made all the data entry cells unlocked and the rest of the spreadsheet locked (cells with formulas etc.)

I would like to be able to insert rows to the protect sheet which I have managed to achieve but when I insert the row the formulas do not update in the row.

View 3 Replies View Related

Hidden Rows In Protected Worksheet?

Jul 21, 2014

I was able to create a macro that allows a user to unhide rows in a protected worksheet without unprotecting the other locked fields (see below). My question is: I want to add to this command so that 1 row can not be "unhidden" if the previous row is still hidden. For example, row 25 can not be unhidden if row 24 is still hidden.

Macro command used:

Private Sub Commandbutton4_Click()
Sub Hide_Rows2h()
ActiveSheet.Unprotect Password:="xxx"
Rows("25").Hidden = Not Rows("25").Hidden
ActiveSheet.Protect Password:="xxx"

View 3 Replies View Related

Find And Copy Rows To New Worksheet

Sep 2, 2008

1. Find a text "State" in column 5 of a active sheet and copy that particular row to a new worksheet "Summary" including the header row.

2. Loop through other worksheets and add results to "Summary worksheet" without the header row.

View 11 Replies View Related

Copying Rows To New Worksheet If Within Date

Jan 26, 2009

I have a spreadsheet with various details sorted by date and i am trying to create a macro that will run whenever the workbook is opened and search the column "Renewal Date" for a date between now and 3 months time. For all rows with a date that meet this criteria it will copy the relevant rows and paste them to a new worksheet with the header. The renewal date column could be added to so i need the macro to run until it meets an empty cell. I have attached an example workbook with cutdown data and my attempt at the macro.

View 4 Replies View Related







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