Code To Hide Rows When One Cell Is Empty And Other Is Not

Aug 1, 2007

I am trying to run a macro that will hide rows when one cell is empty and another is not. Example: hide row when cell g is empty, but cell b is not. Or something to that effect. So far I've only used this code, but I would like to know how I can modify the code to fit the parameters I need:

Sub HideRows()
On Error Resume Next
With Range("B1:B300")
.EntireRow.Hidden = False
For i = 1 To .Rows.Count
If WorksheetFunction. Sum(.Rows(i)) = 0 Then
.Rows(i).EntireRow.Hidden = True
End If
Next i
End With
End Sub

View 3 Replies


ADVERTISEMENT

Hide Rows Based On Cell Value Being Empty

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

Hide Blank/Empty Rows & Shown/Unhide Non Empty Ones

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

Excel 2010 :: Macro - Hide Rows Based On Empty Cell

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

Code Only Deleting One Empty Row Instead Of All Empty Rows

Jan 22, 2012

I am using the following block of code, which cycles through the data and first deletes any cell with "Legal:" in it, and then cycles through again and deletes any row where the cell is blank.

The problem is that within the data, there are some locations where there are two blank rows in a row. When the code runs through, it deletes the FIRST blank row only, not the second. I Need ALL blank rows within the data set to be deleted.

Code:
Sub ModifyNewData()
Dim r As Range, rAll As Range
Dim WS As Worksheet
Dim iLast As Integer

[Code] ........

Also, if there is a way to write looking for blank rows into the first block of code looking for "Legal:" that would be cool too.

If not, just deleting all the blank rows is good. Right now, I have to have the second block of code run twice to get rid of the remaining blank rows.

View 4 Replies View Related

Fast Macro To Hide Empty Rows

Mar 22, 2014

Im looking for a macro that hide empty rows. I found some simple macro but that are long to execute. While looking for a faster code, I found two codes that work pretty fast. But as I don't understand VBA I am not able to adjust them to my situation.

First macro: I am able to specify my range (B6:B77), but the macro applies to blank cells and I need to apply to "" cells.

[Code] .....

Second macro: very fast as well. Here, it applies to "" cells, but I am unable to specify a range. So the rows 1 to 4, which are empty, are hidden but should not.

[Code] .....

View 11 Replies View Related

Hide Worksheets And Rows Based On Empty Cells?

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

Loop Through Selected Worksheets And Hide Empty Rows On Each?

Jun 22, 2014

I have a workbook with over 70 tabs whose position shouldn't be changed. Some of these tabs are colored in yellow (sorting by tab color is not allowed). I need to select these yellow tabs first and loop through them (only yellow tabs) and hide empty rows in the range of A1: G 50 on each of them. Grouping sheets wwon't work because each tab has different last row with data within that range.

View 6 Replies View Related

Vba To Select Colored Tabs And Hide Empty Rows Within Range In Each Tab?

Jun 23, 2014

I need to select all yellow tabs (color code 6) in a workbook with over 70 tabs and hide all empty rows within A1:I36 on each of these yellow tabs. the position of the tabs needs to be unchanged (sorting by tab color not allowed). I got this code from another excel forum but somehow it only works when i select one yellow tab and run it and the code only works on the one yellow tab i selected. can fix this code so that it can loop through all tabs (yellow and non color) and do what i mentioned above for each yellow tab?

Sub HideMT()
Dim Ws As Worksheet
Dim wsColor As Long

[Code].....

View 2 Replies View Related

Hide/Filter Rows Containing Formulas, But No Data. Empty Text

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

Hide Row If Cell Is Empty

Jul 28, 2009

I'm looking for a way to hide a row if the cell is column A is empty. On the attached sheet I'm looking to hide the empty rows below the last vehicle registration.

View 2 Replies View Related

Hide Row If A Certain Cell In This Row Is Empty

Jan 12, 2010

I have this code as mentioned below.
If a cell in column U is empty, I mean really empty, so no zero but a blank cell, I want to hide the whole row. Is this possible?

Sub kleuren()
For Each c In [U3:U500]
If c "" Then
Select Case c - Date
Case Is

View 9 Replies View Related

Hiding Empty Rows Via VBA Code?

Jul 10, 2014

automatically hiding empty rows on my table... though the table has been referenced via string of formulas which I added up on my worksheet.. Some of the rows on my table are empty and I tried a VBA code on automatically deleting the rows but then I realized that if that certain 'referenced' string of that cell is not empty anymore, that would cause me much trouble.. so basically I need a code that would automatically hide the rows that displays a result of "" and unhides when it is not empty.

View 14 Replies View Related

Code To Delete All Empty Rows

May 20, 2014

I need a code that I can run that will delete all rows that have nothing in at the bottom of the sheet. What I mean is I will be printing a file and because rows have been deleted that had data in a lot of sheets are being printed and wasted. So what I need is a before close code or something that will actually make the last row with data in the last row!

View 2 Replies View Related

Code To Delete Empty Rows

May 7, 2007

I have a database and at times I have empty rows. I am trying to write code that looks at the database (it may change daily) and deletes the rows that are empty. The code I've written does NOT work:

Sub emptyrows()
Dim emptyrows As Object
Set emptyrows = Cells(65536, 255)
If Rows = "" Then
Selection.Delete Shift:=xlUp
End If
End Sub

View 9 Replies View Related

Insert Empty Rows Code

Jun 26, 2007

Can VBA code that inserts between written rows a certain number of empty rows (and to be able to specify somewhere in the code the number of rows to be inserted)

View 9 Replies View Related

Hide Row If Corresponding Cell In Column C Is Not Empty?

Sep 3, 2012

I want to hide the row when value "x" is entered in column C

I tried this code in the sheet, but nothing happens.......

VB:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 3 Then
ThisRow = Target.Row
If Not Target.Value = "" Then
ThisRow.EntireRow.Hidden = True
End If
End If
End Sub

So when a "x" is entered in cell (115,C), row 115 must be hidden. When a "x" is entered in cell((9,C) row 9 must be hidden to, and so on. When a cell in an other column is changed, the macro does not need to start.

View 4 Replies View Related

Total Data- Having A VB Code To Delete Empty Rows

Mar 7, 2007

The spreadsheet I am working in has $ which I need to total. The information, or rows, can vary from day to day so there is no static place to have a cell with an @sum. Management wants me to add a pretty line that says

'Total' in one cell and the $ next to it. I have about 30 sheets with columns to total. Yuck, takes forever.

How do all of you total if you don't know the last row? At the top?

I thought it might work to put an @Sum in the last row of the spreadsheet, in the column

I need to total, and having a VB code to delete empty rows. That would be automatic.

View 9 Replies View Related

A Code That Hide Rows Conditionally

Aug 4, 2007

The below sheet sometimes returns blank (“”) cells in columns C:F. I’m looking for a code that hides the rows 113: to 117 when column C:F (all of them) are empty (“”) for that row. In the above example rows 115:117 should be hidden....

View 9 Replies View Related

Code To Hide All Rows Not Containing Keyword

Jul 7, 2009

I have a sheet with 3 ranges of data in Sheet4, being partcode (B20:B20000), description (C20:C20000) and price (D20:D20000).

What I'd like is a macro to take a keyword entered in Sheet4!C17, search the whole range (B20:D20000), and hide the rows that don't contain the keyword.

Autofilter would be the logical method to my way of thinking, but can that work with a keyword entered in a cell, and can it all be hidden in a macro behind a control button?

View 9 Replies View Related

Speed Up Code To Programmatically Hide Rows?

Feb 16, 2013

I have a very complex report with a variable numbers of rows in multiple sections. I am evaluating whether to display each row with a formula in the first column (those that I don't want to display have a "H" in the first column).

The following code works fine, but it takes forever. Ideally I wanted to trigger the code on the Worksheet_Activate() event but its so inefficient that I can't do that.

VB:
On Error Goto Errorhandling
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
Application.Calculation = xlManual
Application.StatusBar = "Processing..."

[Code] .....

View 4 Replies View Related

VBA Code To Hide Unhide A Number Of Rows

Sep 15, 2004

I am trying to create an input sheet with a specified number of rows. My plan was to hide all the empty rows in the table and ask how many rows were required in the table. A button would then be pressed to unhide all the relevant rows thus giving a table of the correct size. My best effort so far looks something like this:

Sub UnhideRows()
Dim i As Integer
Dim myRow As Integer
myRow = Range("A1") + 2
Application. ScreenUpdating = False
For i = Selection.Rows.Count To 1 Step -1
If WorksheetFunction. Sum(Selection.Rows(i)) > myRow Then
Selection.Rows(i).EntireRow.Hidden = True
End If
Next i
Application.ScreenUpdating = True
End Sub

the cell to give the size of the table is A1 and the table starts in A3. The first cell in each row has an index number (=row() - 2), all other cells would be empty to begin with. My attempt did not work.

View 5 Replies View Related

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 View Related

VB Code - How To Hide Rows Automatically If Certain Conditions Met (in Button)

Jun 12, 2014

I'm trying to create a condition that hides rows when certain cells are equal to zero (column D + E + F). Anything else, I would like those rows to remain visible. Ideally, I'd like for this to be applied with the use of a macro (button) and then also deactivated when not desired (possibly another button). I've seen some varieties of code for this but none have worked flawlessly yet. An example sheet I would like for this to work on is my "ADM" sheet", among others.

View 2 Replies View Related

Code Runs When I Hide Rows Or Delete Cells

Aug 2, 2008

So i don't have any events that i know of that should trigger my macros to run. But everytime i delete a cell, or hide rows, it seems all my code/macros just auto run. And i had a few times when i hid the rows, the code would start, and then my excel application would crash.

View 4 Replies View Related

Enhance Existing Code To Hide Rows Between Specific Text

Mar 24, 2014

I have a workbook which contains 8 worksheets. I want to able to run a macro that looks for specific words in column A of each worksheet and hides any rows in between the specified words. The following code works except if a worksheet does not contain the specified words. Is there a better way to accomplish this?

Attached is an example of the spreadsheet. The code below works just fine on the tabs highlighted in green, but halts on the tab highlighted in red. The tab highlighted in yellow is showing you the rows I need to hide.

Example.xlsx‎

View 5 Replies View Related

Macro Code Automatically To Hide Rows Based Condition

Apr 26, 2008

I have a spreadsheet that calculates percentages and then outputs the results to a pie chart. There are 9 different percentages being graphed in cells A41 to A49. The chart looks weird if any of the percentages end up being 0, so I have the formula set to add 0.00001 to each calculation (so they show up as 0% and display on the chart as 0%, but truly are 0.00001). I would like it so that if any of these 9 percentages ends up being 0 (or really 0.00001) that the row automatically hides and thus won't display on the pie chart. How can I create a macro that automatically runs to accomplish this, and automatically updates as percentages are recalculated.

View 3 Replies View Related

Run Code Dependent On Cell Being Empty

Sep 17, 2007

How do i write a line in a macro that basically says:- If this box say (m1) has data in it follow the next procedure that i have designed (which is copying data from one spreadsheet to another and re-designing it). However if this set box is empty do not copy anymore information over.

View 2 Replies View Related

VBA Macro Code To Copy Cell A1 And Paste In First Empty Cell Down Column D?

Jul 30, 2013

Is this possible using code: Copy Cell A1 and Paste in first empty Cell down Column D. This would be connected to a command button. Both Cell A1 and Column D are on the same spreadsheet.

View 3 Replies View Related

VBA Code To Goto First Empty Cell On Opening A Workbook.

Mar 3, 2009

Is it possible to have excel 2007 open a workbook, activate the first sheet "output" and activate the first empty cell in column A - ready for user input? i.e.

I want my user to open workbook "EHB Stock" and automatically the first empty cell in column A of worksheet "output" is activated and ready for input.

The rest of my code is:

View 2 Replies View Related







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