Hide Rows Based On Multiple Columns Formula Returning Empty Text ""
May 13, 2008
I would like to rows based on multiple column conditions criteria. ie., if the columns N, O, P values are "", then hide the particular row. The logic given in the website here, i tried But, it is not 100% working. It works for a few rows at the start of the database & it works for the rows at the end of the database. In between, for a few rows, even if the column values are "" it does not hide those rows.
View 2 Replies
ADVERTISEMENT
Jan 29, 2013
a code that will search each cell across 4 columns and hide the row only if all cells are blank. The macro should search columns "b", "c", "e", and "f" to display all rows where at least one of the cells has a value.
Ex.
Col.B Col.C Col.D Col.E Col.F
1. 123 xxxxx 150
2. 56 xxxxx 50
3. (blank) (blank) xxxxx (blank) (blank)
In this ex. row 3 would be hidden.
View 1 Replies
View Related
Mar 22, 2013
I am working on a time management sheet for my company. I need to be able to click a button and have the file search 3 columns for a persons initials and then hide all the rows where the initials are not in at least one of the three columns. I seem to have no problem getting it to work for one column at a time, but as soon as I try to search more than one it all falls apart.
I will also need to create an unhide all button to reset the sheet after the macro has been run.
I have attached a sample of the sheet below. Each project needs to have 3 rows to show the schedule and budget broken down by each team member.
Project Number
Address
Service
PM
SS
[Code]....
View 5 Replies
View Related
Oct 24, 2012
Using the following code to remove empty rows based on whether a specific range of columns is empty. The code works if the cell has a zero, but not when the cell is blank. An example of the data is attached.
VB:
Public Sub DelRows2()
Dim Cel As Range, searchStr, FirstCell As String
Dim searchRange As Range, DeleteRange As Range
[Code].....
View 1 Replies
View Related
Dec 15, 2008
I have read other posts about this but I am still a little confused as most macros that have been made are tweaked to suite the users individual needs.
What I want is something like this to work in the active sheet:
If cells D2:D55 = ""
Then Hide.EntireRow
If cells D2:D55 = "has any value"
Then Show.EntireRow
The values in D2:D55 are populated by a VLOOKUP depending on what someone chooses in a drop down validation list, however not all the rows are always required so I would like to hide them to save some space on my form.
View 9 Replies
View Related
Jul 2, 2014
My company has a canned template for some of the work we do and to avoid wasting too much paper I wanted to insert some extra code into an already programmed macro button (which sets the page breaks) to hide forms (both as individual worksheets and rows within separate worksheets) if the field that ought to auto-fill them is left empty.
View 3 Replies
View Related
May 8, 2008
I am developing a spreadsheet that, once all the code is run has numerous sheets added. On these sheets I have a significant number of rows that contain no data and could be hidden (I dont want to remove them, because later I need to re- import all these rows back to my master sheet). I tried code I found in the forum to hide a row if it is empty but this doesnt work as some of the cells contain formulaes referencing back to another sheet that is hidden.
I am trying, and failing, to write code to hide a row that contains no actual data, but still has formulas in some of the cells.
View 4 Replies
View Related
Feb 27, 2012
(Excel 2010): Hide row if cell C in this row is empty.
I've just started using macros and I'm sure there is one for this problem.
View 5 Replies
View Related
Oct 8, 2013
I'm working on a database right now for our school, similar to a directory. What I want to do is be able to look up what items a staff member has been issued and when/if they were returned. What I have right now is one sheet with all the information about the staff that looks like the one below. I want to create a separate sheet where you could look up a staff member by last name and can find all the information in the array relative the the specific cell looked up.
Last Name
First Name
Position
Building
Items
So, on the "lookup" sheet, I want to be able to input either Doe and have the look up return cells B2:E6, or if I input Smith it should return cells B7:D11.
I've been experimenting with Vlookup, hlookup, index, and match but haven't been able to get exactly what I want..
View 8 Replies
View Related
Apr 24, 2014
I'm attempting to match a name with the row and column numbers of data that is laid out in a grid format. It looks something like this:
Denson
Matthews
Smith
[Code]....
This works just fine, except for the fact that once in awhile the same name will appear in the grid twice, and the row and column returned will be off (it will return the row for one of the Smiths, for example, and the column for the other).
I know why this is occurring so I'm looking for a better way to do this. The good part is that I don't necessarily need both locations returned for each "Smith" ; I just need the correct row and column for at least one of the instances. I'm looking for a better way to do this.
View 1 Replies
View Related
Jul 10, 2008
I have two columns of data as follows:
10:57:42 273
10:57:42 263
10:57:42 253
10:57:42 241
10:57:37 273
10:57:37 243
10:57:37 249
10:57:37 261
10:57:37 253
11:04:47 241
11:04:47 253
11:04:47 263
10:54:31 254
10:54:31 240
10:54:31 265.......
View 9 Replies
View Related
Sep 3, 2006
I am getting values for my excel sheet from another department excel sheet . everything works fine. If there is no values in the rows in the Department sheet, then i need to hide the rows in my sheet. How to code this in VBA. When they add values to the rows then i should make the rows visble here. Kindy give me a sample of vba code to this or suggest me to solve.
View 9 Replies
View Related
Apr 11, 2009
I need to filter out rows, based on a specific value in column A, (documents on file for the clients), then check column C (last name) and D (client first name), which can have the same client listed multiple times, based on how many different documents are on file ......
View 12 Replies
View Related
Jun 30, 2006
way to automatically hide a row or column based on a specified cell.
Or even something general like: Hide all rows with cells evaluating to #n/a.
View 9 Replies
View Related
Sep 28, 2011
On my 'Report' worksheet in cells I1 to Z1 the formula returns "" if there's no data in that month or "1" to "18" depending on which month it is. e.g. if it's a 12 month accounting period then 6 of the columns will have no data in them and will have "" in row 1, the other columns will return 1 - 12 in row 1; if it's a 15 month accounting period then 3 of the columns will have no data in them and "" in row 1 and the other columns will return 1 - 15.
The VBA code below is part of a longer macro on my 'Data' worksheet. The rest of the code works fine and I just wanted to add this bit to hide the blank columns on the 'Report' page.
[VBA]
Private Sub Worksheet_Change(ByVal Target As Range)
Application.Calculate
With Sheets("Report")
Dim i As Integer
If Intersect(Target, Range("I1:Z1")) Is Nothing Then
Else
For i = 9 To 26
[Code] ........
View 4 Replies
View Related
Mar 19, 2014
I am building a sheet to display deadlines (rows) by project (columns). I would like to have it automatically hide the rows and columns based on the date of the deadline. If the deadline is today or 1 week from today the associated rows and columns should be visible, otherwise, I would like to hide them.
My table is A1:N9, with A1 being a blank, row names A2:A9, and column names B1:N1.
View 4 Replies
View Related
Jul 30, 2007
I need to show hidden columns based on the date I entered. For example, if I entered "1/1/1990" on a1 as the starting date and "4/30/1990" on b1 as the ending date. I want Excel to show the columns that are covered by the date, thus it shows Jan, Feb, March and April. How do I do that? Here's an example attachment. In here Sheet 1 is the starting point, the highlighted cells is where I enter the date. the Result sheet shows what I want Excel to show me when I have a date entered.
View 8 Replies
View Related
Jan 10, 2009
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 13 Then Exit Sub
If InStr(Target.Value, "Other (specify in next column)") Then
Columns("N").Hidden = False
ElseIf WorksheetFunction. CountIf(Columns("M"), "Other (specify in next column)") = 0 Then
Columns("N").Hidden = True
End If
End Sub
but I have a lot of columns that I need to perform as above and I have put the code together as below
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Column = 13 And InStr(Target.Value, "Other (specify in next column)") Then
Columns("N").Hidden = False
ElseIf WorksheetFunction.CountIf(Columns("M"), "Other (specify in next column)") = 0 Then.................
Using the above code, when I selected more than one cell anywhere in my workbook and pressed delete I was bugging out with a runtime error 13 message. You can see from the above code that I inserted "On Error Resume Next" - this got rid of the runtime error 13 message, but now when I select more than one cell and press delete, hidden columns are incorrectly revealed in my worksheet. how I can extend the working code at the top of this posting so that it works for a number of different columns in my Worksheet i.e. without the runtime error 13 occurring and without columns being incorrectly revealed.
View 8 Replies
View Related
Mar 8, 2014
I have a very large table and i need to be able to Hide/show specific ranges based on:
Filter +and+ specific cell values in columns
brief example of the table : tablee.png
So...
1. Filter Column "B" (in this case we select "HELPING")
2. Auto hide/show collumns. - IF "C1" = "Required" THAN Show "C:E", IF "C1" ="N/A" , HIDE "C:E" and so on for every column like above.
There are over 80 columns like the "C:E" range. and I only need to show those that are "Required".
View 1 Replies
View Related
Oct 28, 2009
The workbook has multiple sheets, and a cover sheet. Each project has a sheet and the status, costs, updates ect. are updated to the cover sheet.
Problem:
What happens is there is a cell which has conditional formatting on it, e.g. If text is Green then colour cell green, if cell text is amber then colour amber and if cell text is Red then colour Red. This works fine.
If the cell text is green then I need a number of rows hidden, if it is amber or red then show the rows.
I have found the following
Sub
If Target.Value = 1 Then
Rows(Target.Row).RowHeight=0
Else
End If
End Sub
Which I have changed to:
Sub
If F4.Value = Green Then
Rows("5,6,7,8,9,10.Row).RowHeight=0
Else
End If
End Sub
So this does not work, need to know why and how to make this work for each sheet I am on e.g. ActiveWorksheet?
View 9 Replies
View Related
Feb 20, 2013
I have a worksheet that contains 15 instances of a repeated table over 700 rows. Each table is 45 rows in size and is housed in between the natural page breaks in the spreadsheet.
These tables are populated from data form another worksheet but may not all be used (8 out of the 15 may be used but will always start from table 1 and there will be no missed tables).
In the very top right of the table is a cell value that is only displayed if the table is in use, so will be blank if not used.
Code that will hide multiple rows (45) based on a cell value being blank.
View 2 Replies
View Related
Mar 7, 2008
I want by using some code I've seen on this forum or using the macro writer and then tweaking the code. So with that said, I've written the attached code but I know there is probably an easier way to write it. It cycles through about 12 sheets using the same below code, but I didn't list that code.
Sub Hide_Rows()
Dim i As Integer
For i = 3 To 418
Sheets("AFA - UMBI").Select
If ActiveSheet. Range("b" & i).Value = "2008-2" Then
Rows(i & ":" & i).EntireRow.Hidden = True
ElseIf ActiveSheet.Range("b" & i).Value = "2008-3" Then
Rows(i & ":" & i).EntireRow.Hidden = True
ElseIf ActiveSheet.Range("b" & i).Value = "2008-4" Then
Rows(i & ":" & i).EntireRow.Hidden = True
ElseIf ActiveSheet.Range("b" & i).Value = "2009-1" Then
Rows(i & ":" & i).EntireRow.Hidden = True
ElseIf ActiveSheet.Range("b" & i).Value = "2009-2" Then
Rows(i & ":" & i).EntireRow.Hidden = True
ElseIf ActiveSheet.Range("b" & i).Value = "2009-3" Then
Rows(i & ":" & i).EntireRow.Hidden = True
ElseIf ActiveSheet.Range("b" & i).Value = "2009-4" Then
Rows(i & ":" & i).EntireRow.Hidden = True
End If
Next i
End Sub
View 7 Replies
View Related
Jun 11, 2014
I have a spreadsheet which contains our fleet information covering many vehicles in one list. The teams for these vehicles are listed in Column A. What I would like to have is:
A dropdown box to pick from All, or the various teams in A1To hide every row that doesn't match the selection of A1To reveal every row that contains the selection for A1 at any point in column A. e.g: "*Dog Team*"
This is because something might be listed as "Moved from Dog Team" or "Dog Team, temporarily on loan", or "Dog Team V66.m"
I have taken a small sample of the data I will be working from and made a very simple drop down box which displays the pages of information for the vehicles by hiding/unhiding blocks of columns:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$2" Then
If Range("A2") = "MOT/Service" Then
ActiveSheet.Columns("A:P").EntireColumn.Hidden = False
ActiveSheet.Columns("Q:CE").EntireColumn.Hidden = True
ElseIf Range("A2") = "Contact Details" Then
[Code]....
But because there is also filters on the sheet, meaning the positions of the entries based on rows can change, I need it to search for the "*xyzzy*" method, but am unsure how to do this! While just using the Filters will work, due to some human limitations I have been asked to have a specific drop down box in a very specific location with instructions next to it.
View 1 Replies
View Related
May 23, 2012
I would like to hide rows for data that does not meet specific criteria. For example: If a user selects room number 101 from a drop down in B1, I want to filter data in range A3:F1000 to show me only room 101 rows (A column) where C column contains a value larger than zero OR D column contains a value larger than zero OR E column contains a value larger than zero. I do not want it to return rows where 101 may be in other columns beside A.
Sample:
A B C D E F
__________________________________
1 ROOM: 101
2
3
4 101 XX 1.2 0 0 P
16 101 YA 0 0 1.1 L
23 101 JJ 3.2 2.1 0 L
55 101 JJ 0 0 1 P
So, if a row contains 101 in column A and all three values in columns C, D, and E equal 0, then those rows will be hidden.
View 8 Replies
View Related
Aug 6, 2009
I am trying to hide/show entire rows of a range based on the conditional formatting in the row. I want all rows with at least one overdue training cell (indicated by a red cell) displayed, and rows with no overdue training hidden. The conditional formatting formulas vary greatly, but always result in a white (unchanged), yellow, or red cell. Here is a sample picture for reference:
[url]
The CF formulas vary based mostly on two major factors: the frequency of the requirement found in Column "C" (Monthly, Quarterly, Semi-Annual, or Annual Requirement), and the personnel's arrival on site or date of departure (wheels up) found in Rows("3:4"). Each training class has two rows. The first row indicates the last time the class was completed, and the second row shows when it is due next. Both rows have to be displayed/hidden based on the second row's conditional formatting. Here is the code I am using right now: ...
View 3 Replies
View Related
Jun 17, 2014
I have the following code which works perfectly. It needs to hide rows 3:60 based on the value in K2.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("K2")) Is Nothing Then
If Not IsNumeric(Range("K2").Value) Or Range("K2").Value = "" Then
Range("A3:A60").EntireRow.Hidden = True
Else
Range("A3:A60").EntireRow.Hidden = False
End If
End If
End Sub
The problem I am experiencing is that I want to use a formula in K2. K2 must get its value from a cell on a different sheet.
If I manually delete the value in K2 the rows hide and if I manually enter a 1 in K2 the rows unhide, however I want to enter a formula in K2 that will result in either a 1 or blank cell.
View 3 Replies
View Related
Mar 25, 2014
I am trying to populate the 2 tables from excel to word. I will be getting the excel file with tables in various sheets. One sheet consist of 2 tables that will be inserted to one word document. So if there are 2 sheets then I will have the tables inserted in the 2 word document. In the excel sheet I have attached, there are 2 sheets with tables in each of them. I have written the code to copy and paste the table to word doc from (general) range A1:G4 (Table 1) and A9:H18 (Table 2) that has empty rows and columns selected. But there are empty rows and columns inserted since the table range is not same sheetwise. I would like get the empty rows and columns deleted in the word table.
Find the attached sample excel sheet and the word documents.
DeleteEmptyRows(Sample).xlsx
Sheet1.doc
Sheet2.doc
View 2 Replies
View Related
Feb 26, 2014
I am hoping to create a drop down list of months in one sheet, and when I select a certain month, columns in about 10 other worksheets in the same workbook will either hide or unhide columns...
The spreadsheet is laid out with columns (C-N) for each month in the year, for actuals, then columns for budget and budget variance (O-P), then YTD Actual, YTD Budget and YTD Variance. When I select September, for example, I want October-December to hide, and leave Jan-Sep unhidden, while keeping the budget, YTD and variance columns.
Is there a VBA code that can achieve this?
View 14 Replies
View Related
Oct 2, 2007
I have a range that is filled with formulae and the the number of rows in the range with data changes as the formulae reevaluate the inputs. So the rows with visible data changes between one row and 200 rows. By looking at the output in the first column of the range, Col M and finding the last row where the formulae returns data, I need a way of selecting across the columns of the range (M1 through to Qi) where i is the number of rows that have data in.
View 8 Replies
View Related
Apr 15, 2014
I have code which will hide columns L to AA if cells are empty. It works fine.
I want to modify it to consider rows 3 onward down. It means even if rows 1 & 2 are populated and row 3 onwards down are empty, then it will hide it.
I tried but it is not working .....
View 3 Replies
View Related
