In sheet 1, I have a list of data in A2:D21. In sheet 2, I have formulas in the same range that "paste link" the data. If, there is no data entered in any entire row of the same range in sheet 1, then I want the entire row to hide automatically in sheet 2. In fact, unless there is something entered to start with, I do not want any of the rows to be visible. Is this possible and how?
View 2 Replies
I have a seating plan for my students that has all sorts of data in it and I want to hide certain rows from students. However, they are not all in a group, they are spread out. So for example I have row 3 with student name, row 4 with their target grade, row 5 with their current grade, then row 6 is another student with data on 7 and 8. After that I might have a gap and then the next student on row 10 with data on 11 and 12. I want to toggle on and off the data and leave the student seats. I'm thinking that Subtotals would be the best way, but not sure how to implement it. Do I put row headings on the side? Also I haven't used subtotals since Excel 2003.
View 1 Replies View RelatedOperating System: Windows XP, Excel version: 2003
Aim: To create a Macro to hide all rows where the date in column D is before today. Column D has about 600 rows.
Current solution:
Code:
Sub Hide_Old2()
'Worksheet name
With Worksheets("Schedule")
'set start of date range
Set rngStart = .Range("D2")
'find end of date range
[Code] .....
The problem with this solution is its speed, or lack thereof. It causes the screen to hang and flicker while it cylces through. Is there some way to create a range based on the date and hide the range? or another solution?
Note: Autofilter is not an option, as the spreadsheet with the dates needs to be kept simple for other stakholders and the macro is being run from another sheet.
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
I have one worksheet that contains a large table. I'm using VLOOKUP to spread each row of the table to separate worksheets.
When VLOOKUP refers to an empty cell, is there a way to set that row to be hidden?
Also, if VLOOKUP returns data to a cell, is there a way for Excel to automatically set the row height to display all of the linked data in that cell?
I have one worksheet that contains a large table. I'm using VLOOKUP to spread each row of the table to separate worksheets.
When VLOOKUP refers to an empty cell, is there a way to set that row to be hidden?
Also, if VLOOKUP returns data to a cell, is there a way for Excel to automatically set the row height to display all of the linked data in that cell? There is only one column of data.
I want to autohide any row that does not have a number in the Current # of Bins column.
View 2 Replies View RelatedI wrote a macro to select multiple sheets by name and hide them, but I keep getting the following error message:
"Object variable or with block variable not set".
Below is the macro:
Sub HideSheets1A()
Dim ws As Worksheet
Application.DisplayAlerts = False
If ws.Name = "Variance Evaluation" Or "Investment" Or "Costs & Incentives" Or "Revenues Total" Then ws.Visible = False
End Sub
I use Excel 2003
I'm using this VBA code which is hiding cells in rows 59:111. Some of these cells have formulas and some don't.
Private Sub Hide_Unhide()
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
For i = 59 To 111
Rows(i).Hidden = (Cells(i, "b").Value = 0) + (Cells(i, "b").Value = "")
Next i
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
Here is the problem I'm running into: I want this to run in the background so the user won't have to click anything to hide/unhide the cells whenever the options in the drop down list are changed and the data expands/collapses.
I've got a spread sheet at work that is shared between 4 people. It's password protected and its been protected so only four of us can edit it. It was working fine until yesterday for no reason the auto filter stopped working. The drop down arrows are still there but nothing happens when you click on them?
The sheet was made on excel 2003 but one of the users uses the newer excel which we will all eventually get.
I have inherited a number of databases in work (running Office 2003). It has quickly become apparant that a vast amount of work is duplicated and so i am trying to cut down the data input and therefore the possible errors.......
I have narrowed most of the work down and now have a major worksheet (is that what you call a complete Excel file) named "master database" and several over minor files....
Currently what i am trying to do is to get the minor files (68 seperate files) to auto populate an area of the master database. I will try to explain it below...
1. Minor database has 2 columns with data i require to auto populate the master database. (1 column (B) is called 'off', the 2nd column (C) is called 'on').
2. A number will be inputed manually into either 'B' only or 'B and C' columns, depending on the criteria of the job..
3. The criteria of the job is dictated by column (Z) where the text 'A' or 'ATL' is inputed
4. The master database i would like to add up the numbers inputed as a total from columns 'off' and 'on' and place them into seperate columns 'E' and 'G' of the master database.
5. IF column (Z) shows 'A' then only column (B) 'off' is to be calculated and put into the master database at column (E)
6. IF column (Z) shows 'ATL' then BOTH columns (B and C) 'on' AND 'off' are to be added together and column (G) populated on the master database.....
To make matters more complex. An expiry date is shown on the master database at columns (D) and (F).
IF column (E) does not exceed 12 by the expiry date, i would like the cell (D) to turn red
IF column (G) does not exceed 10 by the expiry date, I would like the cell (F) to turn red
I'm trying to find a way of hiding every sixth row on my sheet, however, no matter what i try I can't seem to get it to work.
I want to be able to click a macro button that will auto hide every sixth row. But I don't want to have to input every row reference in VB.
I am creating a copy of a spreadsheet (table format) in a separate workbook using formulas that will update the copy as the original updates. I would like to auto hide the rows that have the value "Returned" in column G. I have columns A - G , rows 2 - 2000. The value "Returned" is the result of a simple = formula. Is there some way to accomplish this?
View 4 Replies View RelatedI've attached a screenshot to illustate what I am doing and a one tab version of the workbook. I've had to do it in a zip folder as they were too big individually.
I've got a table that starts at row 12 and finishes at row 217 and the users enter information into the rows over a year. The creator of the sheet has set it up so there is a "z" in the second cell of each row and as this cell is overtyped with the new information the row changes colour and is included in the selected print macro that is set up.
I want to add in a macro that changes the row height to 0 based on the "z" being present in the row above 2nd cell. So all that is showing in the table are the rows that have info in them and one blank one underneath. So everytime a new row of info is entered either a new line will reveal itself underneath or there is a control button on the sheet that the user can press to reveal a new empty line.
I don't know how to write VB, but I've found some code online that claims to do what I need, but I need it to be altered to use the presence of the "z" in the row above (2nd column) as the trigger for the rule:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Value = 1 Then
Rows(Target.Row).RowHeight = 0
Else
End If
End Sub
Or should I be starting off with minimised rows and changing it so the height increases as the "z" in the row above is overtyped?
Can this happen automatically as the z is overtyped or does the macro need to be triggered by a control button for example?
Is there a better way to do this? I don't want to get rid of all the extra empty rows and have a macro to create a new row for 2 reasons: 1.They have formulas and macros running set up by the creator that I don't want to mess with and 2. There are 52 sheets in the workbook, 1 for each week of the year and the next sheet takes the information from the previous weeks sheet so on the last sheet, number 52, it has every line that has been entered over the year from week one to week 51 carried over. If I created a new row on week2, I would have to then create that row on every sheet following week 2 and I think that would make it more complicated. I would need the macro to be able to run on any of the 52 sheets.
Private Sub Worksheet_Change(ByVal Target As Range)
Cells.RowHeight = 12.27
Range(Rows(Target.Row + 2), Rows(217)).RowHeight = 0
End Sub
I have the following macro to autohide some rows with no data, however its remove rows starting from column A.
[Code] .....
How do i fix this to only hide the rows in the range (H4:V50)?
I have a very long spreadsheet and want to keep it manageable by hiding rows until they are needed.
Example:
Column
Row A B C D E
1 Line1
2 Reg
3 OT
4 Min
5 Line2
6 Reg
7 OT
8 Min
I want to click on A1 and auto hide rows 2-4. Then I want to click on A1 and auto unhide rows 2-4. Then I would copy the idea to Line2, etc.
I want to click on a cell and automatically hide the 3 rows underneath where I clicked. Then unhide them when clicked again.
I will assign the vba code to the specific cells of the sheet where I need it. But it will always hide/unhide the 3 rows underneath the click.
set a formula to auto calculate the staggered rent for the month. When I change the date, it will tell me for this month I should charge according to the rates for the year.
Rent for the month
Start Date Year 1 Year 2 Year 3 01/07/14 Explanation
01/08/13 10 20 30 10 < 1 yr = 10
01/07/13 40 50 60 50 enter 2nd yr = 50
16/07/13 70 80 90 76.29 (15/31*70)+(16/31*80)
16/07/13 10 20 30 15.16 (15/31*10)+(16/31*20)
formula or vba using Excel 2003.
Running excel 2003 and have a number of sheets where i want buttons along the headings to sort a selection of rows.
For Eaxample one sheet i have data in range A6:AD135. i would like to assign a macro to various buttons on row 5 to sort by that column ie button in column b sorts all data in the range by column B.
I am running MS Excel 2003.
A user inputs a string of letters/symbols all meaning different things. These would typically for my case be L, R, +, -, F. These items correspond to certain behaviors. Essentially I'm working on a L-System solver for those of you who are well versed in math. For those of you who aren't, I'll explain a bit.
An L-system starts with a string, for example:
LFL+F+LFL
and then has one or more generators:
L:-RF+LFL+FR-
R:+LF-RFR-FL+
then at each iteration of the system, every L and R in the previous iteration gets replaced with that string. The +/- are incremental angles.
I need to be able to take each iteration and convert it into a series of rows with one character in each row. I need it to do this automatically, and automatically adjust to the proper length of rows (these strings can get thousands of characters long because each iteration of the system converts the Ls and Rs into longer strings containing more of all of those letters.) Once each of those rows are constructed, I need it to run some mathematical operations on each of these rows automatically.
For example, for each + or -, the value in an angle column should change by an increment given elsewhere in the sheet. For each F, the x and y coordinates should change from the previous iteration based on the angle (just a basic d+dcos(ang) or d+dsin(ang) where d is a distance multiplied by a scaling factor).
I have no experience with Macros or any type of scripting, so if I can keep this in an Excel formula, that would be ideal. I know a minimal amount of C++ and Python, but that's the extent of my programming knowledge.
I am trying to automate a process where a series of numbers would get populated according the range values. Also I am trying to get the automation to pick up the next range when finished with first one and continue with the task.
Here's what I have as start info and where I want to get to.
Sheet1
A
B
1
Ranges
[Code] ......
Excel 2003
I have a master list of Players on one sheet B2:B72 and in E2:E72 is a column called Playing and in it is "y" or "n". I need to make a list of the Players that are Playing on another sheet without blank rows (I can do it but it leaves blank rows for the Players that have a "n" from column B. I am using 2003
Here is the formula I am using now: =IF(Players!$E3="y",Players!$B3, " ") but I get blank rows for the player that are not playing.
I have a notepad with lot of information's but when i try loading those info's to excel I am getting message "File Not loaded properly"
Is there a VB code which downloads it to excel and automatically moves to next tab and paste the rest.
I am using excel 2003. My sheet contains Times in column A and numbers in column B. I want to be able to count how many of those rows fall into time periods. Such as how many are from 8:00-9:00 AM. I do have the time column formatted as Time.
Ex:
A1: 3:40 PM
A2: 8:20 AM
A3: 8:55 AM
A4: 10:23 AM
etc.
B1: 23
B2: 34
B3: 12
B4: 35
etc.
So I want to be able to pull that between 8 and 9 AM there are 2 records.
The second part would be (in another cell) to pull the SUM of the numbers that correspond to the previous question. In the previous example it would be the SUM of all corresponding cells (Column B) that fall within the 8 and 9 AM range. Which would be 46 in this example.
Excel 2003 / WinXP. I have a list object with 5 columns, A - E, and all cells have a border round them. I need a macro which will:
1. insert a row below the current row (whatever cell happens to be selected)
2. then remove the left hand border of the cell in column B in the new row,
3. then select the adjacent cell in column C in the new row ready for data entry.
Obviously I cannot specify any absolute addresses in the code, and this is where all my attempts to record a suitable macro fall down.
a VBA problem I have and I do not know how to solve. I use Excel 2003.
I have a workbook with a table; all cells except the table area are locked so at users cannot add any data. The table is on D2: H?. The workbook is password protected (users can only "Select unlocked cells", "Format cells" and "Sort"), and for that reason there is a "Add Rows" macro button so they can add more rows if necessary. It works well.
There is also a "Delete Rows" button, but it does not work as I want it to do. If the table is for example on D2:H4, and I select a cell on row #3 (ex. D3, or E3, or H3) and click on the button it deletes row #3, if I click on the button again it deletes row #4, and if I click on the button again it also deletes row #5 and so on, but it should not do it because all rows below row #4 are locked and should not be deleted.
I found on the Internet a code that I modified, but I do not get it to work. Below are the two macros.
My original macro (it deletes even locked rows):
Sub DeleteRows()
'
ActiveSheet.Unprotect Password:="123"
'
Selection.EntireRow.Delete
'
ActiveSheet.Protect Password:="123", DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, AllowSorting:=True
End Sub
The macro found and modified (the original rows are commented):
Sub DelRows_2()
Dim rngDelete As Range
Do While rngDelete Is Nothing
On Error Resume Next
[Code]....
I know how to separate data into different columns i.e. Marry Johnson into two separate columns; however, I have data in different rows that I need separated into different column. See screen cast [URL]
Using excel 2003 on windows 7 64 bit
I am using Excel 2003.
I have 2 worksheets.
Worksheet 1 is called "Master List Data". Every cell within this worksheet contains a formula so that it matches the cell value contained in a worksheet held within another workbook.
The formula for reference is as follows:
=IF('[CCL Breakdown.xls]MASTER LIST - Active Customers '!A1="","",'[WFX CCL Breakdown.xls]MASTER LIST - Active Customers '!A1) .
e.g.
If Cell A1 on Master List-Active Customer contains no data, Cell A1 in Master List Data would be blank.
If Cell A1 on Master List-Active Customer equals John Smith, Cell A1 in Master List Data would display John Smith.
There are currently non-blank values contained in cells A4:A750. But next week there may be non-blank values is cells A4:A790 (i.e. it will grow each week)
Worksheet 2 is called "Master List Flat. At the moment, I am manually copying and pasting the rows which have non-blank values in cells from Column A from Worksheet 1 into this report (e.g. A4:IV4).
I would like to automate this process and I have created a Macro, but I do not know how to word it so that it will only copy cells with actual data in.
The Macro I have written is below:
Sub IMPORTANDFLATTENDATA()
'
' IMPORTANDFLATTENDATA Macro
' Macro recorded 01/10/2012 by walesb
'
'
Application.ScreenUpdating = False
Sheets("Master List Flat").Select
Rows("4:759").Select
[Code]....
I have two worksheets (Sheet1 and Sheet2). Based on meeting a condition on Sheet1 AND Sheet2, I'd like to use macros to automatically hide rows on Sheet2.
Sheet1 sample:
Type
Selection
[Code]...
The conditions are: If on Sheet1, a Type has the Selection "Include", then any rows on Sheet2 which have that same Type are shown (ie. not hidden).
If on Sheet1, a Type has the Selection "Exclude",then any rows on Sheet2 which have that same Type are hidden.
So in the example tables above, if ONLY Type A = Include, then on Sheet2, the rows for Sample URL1 and Sample URL2 are shown, and the others are hidden.
Also in the example tables above, if Type A = Include AND Type B = Include (and all other Types are Exclude), then on Sheet2, the rows for Sample URL1, Sample URL2 AND Sample URL3 are shown, and the others are hidden.
I'm fine with the concept of If... And... on the same worksheet, it's trying to get it to look at two separate worksheets I'm struggling with!!
I have a workbook with two worksheets, Sheet1 and Sheet 2. Based on the data entered in the 2nd column on Sheet1, I'd like a macro which hides rows for Events which do not meet the criteria entered on Sheet1.
So in the example below, on Sheet2, I only want to see rows where the Country=Scotland, AND the Location=Glasgow, AND the Start Date is greater than (or equal) the Start Date on Sheet1.
Sample of Sheet1
Country
Scotland
Location
Glasgow
Start Date (dd/mm/yyyy)
30/04/2013
[code]....
So in this scenario, only the FIRST row meets the 3 criteria (Scotland, Glasgow, date after 30th April), thus all the other rows should be hidden.
