Writing A Macro To Hide Rows

Sep 23, 2008

I am now trying to hide rows when the data in a column matches that of a cell with a data validation list. Ideally i would like only the rows with matching data to show. When the cell is changed then the visible rows change and when the the entry "total" is selected all the rows are shown. There are several small tables of data on a page so I can't use auto filter. The data areas will also change in size as new entries are added. So the last row in the data tables will increase over time. I will copy and apply the macro to each separate table of data.

As an example cell c19 contains "lwsf"

The data table is in the range c24:q47. the column with matching data is column D so all rows of entries with "lwsf" in column D should be visible and the rest hidden. When c19 is changed to "TF" then the rows visible become those with TF in column D and the rest hidden.

View 14 Replies


ADVERTISEMENT

Writing A Macro That Deletes Blank Rows

Dec 11, 2008

I need help writing a macro. I only have experience recording them, not writing them in VBA.

I need to check columns A and B for blanks. If A and B are both blank, I want the macro to delete the row and move on to check the next row. I need to perform this macro for the entire worksheet.

View 8 Replies View Related

Writing A Cleaner Loop & Toggle Hide/unhide With Button

Oct 12, 2007

What the code does is, when the command button is clicked, if any of the validation list in a particular range is equal to"Select_Product.." or "Select Feature", then hide those rows.

Also, is there a way to toggle hide and unhide rows using one command button. I would like the code to not only hide the rows but when clicked again, it'll unhide it. Is this possible?

Private Sub CommandButton1_Click()

Dim c As Range
Dim d As Range
Dim myRange As Range
Dim featureRange As Range

View 9 Replies View Related

Macro To Hide Rows With 0

Nov 19, 2013

optimize the above Mcro to hide rows with "0".

Sub HideRowsw()

With Excel.Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
.EnableEvents = False
End With

[Code]...

View 1 Replies View Related

Macro To Hide Zero Rows

Jul 22, 2006

I have a series of worksheets in a single workbook. The problem is there are many rows that contain zeros across. The problem is that there is text in that row in case a number does show up there. I need a macro that will hide all of the rows containing zeros or no values.

View 2 Replies View Related

Macro That Should Hide Rows Not Working Well?

Apr 17, 2014

I'm having a problem with my macro .However, if i use the following script:

[Code]......

If i am correct all the rows with a value less than 1 should be hidden right? However, some rows are hidden when they shouldn't be.

Im working on a quotation with different products in it. And the rows with a 0 in it should be hidden and that's pretty much working. But it also hides some of the subtotals(which i dont want) and some stay unhidden.

View 6 Replies View Related

Macro To Hide Rows If 0 Is In Cells

Oct 31, 2007

I just need a quick and easy macro that hides rows that have a 0 in columns H, I, J, K, or L.

View 11 Replies View Related

Any Way To Speed Up A Hide Rows Macro?

Sep 12, 2009

Does anyone know a more efficient way to run this code?

View 7 Replies View Related

Macro To Hide Rows With The Len Function

Oct 22, 2009

I have this macro hiding rows, but not the right ones. I am trying to hide all rows where the Len of a cell in column C is > 1? What am I missing here?

View 4 Replies View Related

Macro To Hide Rows Based On 'Sum'

Dec 26, 2009

I want a code to Hide the row if "sum of numbers in columns three thru last column" = 0. Following is a code I use to delete a row if that is blank. The difference here is that I dont want to to check first two columns and I want to hide them rather than deleting.

View 4 Replies View Related

Macro To Hide Rows If Cell Value Is Same?

Oct 23, 2013

I Have Values in Cells A29 thru F45. I want to hide the entire row if the value in Column C is the same as the value in Column C in the row directly above, i.e, if the value in C30 is the same as C29, Row 30 should be hidden.

Based upon the logic built into the table values. if the value in C30 matched C29, all of the values from C30 thru C45 would match C29 with a need to hide all rows starting at Row 30 and then thru row 45. The values could begin repeating at Cell C31 with the value in C31 matching C30 with a need to hide from row 31 thru 45.

I am thinkin a Selection.AutoFilter type of macro?

View 9 Replies View Related

Hide Rows Macro Crashing

Feb 14, 2014

I had this code working perfectly. However in the last 24 hours it has started freezing on me. Here is the code:

' This code will dynamically hide or unhide rows based on whether a row contains a transaction

Sub hideRows()
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
Application.DisplayAlerts = False

Sheet1.Unprotect Password:="University"

[Code] .....

I call the code into a Worksheet_Change module on a particular sheet (Sheet1). It hides rows between 11 to 1503, but if I innocently select these rows, the spreadsheet freezes on me & I have to kill Excel. I'm guessing I need to add code to counter these do nothing events?

View 1 Replies View Related

Macro To Hide Rows And Columns

Aug 1, 2008

The macro code that will populate and input box and ask you which range of columns and range of rows you wish to hide, hide the columns and advise you via a message box that it has been completed

View 9 Replies View Related

Error With Hide Rows Macro

Apr 17, 2009

Im trying to use the following code to hide rows on my sheet however its just crashes my sheet:

Private Sub CommandButton2_Click()
Dim Ws As Worksheet
BeginRow = 1
ChkCol = 6

For Each Ws In Worksheets
EndRow = Ws.Range("AW" & Rows.Count).End(xlUp).Row
For RowCnt = BeginRow To EndRow
Ws.Cells(RowCnt, ChkCol).EntireRow.Hidden = Ws.Cells(RowCnt, ChkCol).Value = "Open"
Next RowCnt
Next Ws
End Sub
Basically, from row 3 onwards I want to hide rows where AW does not contain 'Open'.

View 9 Replies View Related

Macro Hide / Show Rows

Jul 2, 2009

I'm trying to create a macro that will hide all the rows where the value in column E is equal to zero.

I'm currently using rows 1:700, but I may add to it.

View 9 Replies View Related

Hide Unhide Rows Via Macro

Feb 26, 2010

I have a workbook that has 500 rows. In order to be able to print the spreadsheet, I have added a Macro in that hides any rows that have a "0" in the A column. I then put an if/then formula (ex. if(isblank(A5),0) so that if the cell was blank and 0 would be there and therefore the row would be hidden.

The problem now is that I want a new row to become unhidden everytime the row above has data in it. So, if row 5 gets data put into it, row 6 would become unhidden. The problem I'm running into is that the data in column A is peoples names, and therefore each row will have a different name (i.e. different data).

1. Is there a macro to do this?

2. (This may be a really stupid question, but...) Can you run 2 macros in the same sheet?

View 9 Replies View Related

Macro Button - Auto Hide Rows

Mar 18, 2014

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.

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

Macro To Hide Rows Containing Data In Range

Jul 22, 2009

I have the following Macro that hides rows that contain old dates (in column B) in a range:

View 2 Replies View Related

Macro To Delete (not Hide) Rows And Resort

Sep 28, 2009

I've attached a worksheet with two tabs, "before" and "after".

(a) delete the contents of rows in column "C" which <> 0.
(b) resort the data according to date under column "J" (oldest to newest)
(c) retain the integrity of column "W" which is a running total.

I do not want to hide data. I have too much of it, and I also need to use relative formulas on the sorted data (for example, a running total). I've seen other solutions in this forum for deleting rows etc. but it seems that most of them involve "hiding" information, or using filters which will make it difficult to retain the integrity of forumlas elsewhere on the worksheet.

View 2 Replies View Related

Macro To Hide Rows Based On Cell Value

Jun 17, 2003

Like to create a macro (button) to hide / unhide rows based on a cell value
Example
A1=No then hide roa A
C1=No then hide row c

View 9 Replies View Related

Macro To Hide Rows When A Check Box Is Chosen

Aug 26, 2009

Need a macro to hide two rows when a check box is checked? Is this even possible? I would like rows 44 and 45 to be hidden when the check box next to loan impairment is checked.

View 9 Replies View Related

Macro To Hide Rows Based On 'Sum' Of Certain Cells

Dec 26, 2009

I want a code to Hide the row if "sum of numbers in columns three thru last column" = 0. Means, if the sum of all the cell (except first and second cells in that row) in a row is zero, then that row should be hidden.

View 9 Replies View Related

Hide Rows In VBA Macro By Checking 2 Columns

Oct 8, 2009

I've tried using multiple loops in the forum but cannot seem to figure out how to actually get them to work properly using the conditional VBA codes on two separate worksheets. The first code snippet is checking cell values from row 6 to 148 as such:

Sub Check_Shifts()
'Insure all shift entries are completed
If Range("K6").Value < "1" And Range("I6").Value < "1" And Range("G6").Value < "1" Then
Range("G6").Value = Range("F6").Value
Range("I6").Value = Range("F6").Value
Range("K6").Value = Range("F6").Value
ElseIf Range("K6").Value < "1" And Range("I6").Value < "1" Then
Range("I6").Value = Range("G6").Value
Range("K6").Value = Range("G6").Value
ElseIf Range("K6").Value < "1" Then
Range("K6").Value = Range("I6").Value
End If
If Range("K7").Value < "1" And Range("I7").Value < "1" And Range("G7").Value < "1" Then........................

View 7 Replies View Related

Macro To Hide Rows Based On Column Value

Aug 15, 2006

I have read several related threads and tried to adapt their answers to my application with no success. I am trying to write a macro that will hide rows based on that row's value in a certain column. Specifically, column AB contains sums and if a sum equals 0 I want to hide that row. The sums start at AB5 and go to AB118 but there are 4 gaps in the column at rows 25, 47, 70, and 94.

View 9 Replies View Related

Automating A Macro Range To Hide Rows

Aug 17, 2006

I have a graph in Excel containing the 12 months in column 'A' and data associated with each month in column 'B'. I want to hide rows containing months which have yet to arrive (eg: if we are in August, I want to hide the rows containing September:December). However, I want to change the range of rows that I hide each month (so that when we get to September, I only hide rows containing October:December). My attempts at writing a macro to perform this automatically have failed? This is the script I have written:

fyi - RowStart is the row number associated with January, RowEnd is the row number associated with December and RowNext updates each month and is the row number associated with the next calendar month.

Option Explicit
Dim RowStart As Integer
Dim RowEnd As Integer
Dim RowNext As Integer

Sub Hiderows()
RowStart = sheets("month").Range ("A1")
RowEnd= sheets("month").Range ("A2")
RowNext = sheets("month").Range ("A3")
Sheets("month").Select
Rows("RowStart:RowEnd").Select
Selection.EntireRow.Hidden=False
Rows("RowNext:RowEnd").Select
Selection.EntireRow.Hidden=True
End Sub

View 2 Replies View Related

Macro To Show / Hide Rows On Background Worksheet

Apr 9, 2014

Getting a macro to work. I've looked through the forums pretty extensively but ad I'm not too hot with the old vba, I haven't been able to get it working.

I have two worksheets in the same workbook. The first worksheet, let's call it Input, is one for data entry; and the second one, let's call in Output, is formatted for printing.

There are 8 drop down boxes from a data validation list, that when a particular option or three are selected, I need the Output worksheet to unhide only the rows associated with those options.

In trying to get this all to work, I'd added a function in the cell to the left of each option in the Output page that will show the text "show" when that option is selected on the Input page, or the text "hide" if not selected.

I think I could do this with some time with a clunky and long macro, but would prefer to us some kind of "for each" option to hide rows that have "hide" shown in column A, as I'm looking at a range of 100~ cells.

View 3 Replies View Related

Macro To Auto Hide Rows In Specific Range

Jun 10, 2014

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)?

View 5 Replies View Related

Listbox Macro To Hide / Unhide Rows In Different Sheet

Jun 27, 2014

In the attached abbreviated example, what should the second line in each of the three macros read to direct the hiding/unhiding of rows in the adjacent sheet? Right now I'm trying a If/Then configuration but it's not working. I was able to figure this out using grouped check boxes but a list box should be graphically "cleaner" and hopefully simpler in coding. The original table list is hidden in column "A", if that is important. I could use a combo box but the menu list is short and I think the list box approach will be simpler.... If there is a way to combine the three macros into one.

View 2 Replies View Related

Macro To Hide And Unhide Rows Based On Cell Value

Jan 2, 2014

I am trying to create a macro that will work on my master summary sheet. The sheet pulls information from other worksheets. I would like to have a macro that will look at range E7:E356. If the value is 0 then hide, but if the value changes then unhide the row. I have tried autofilter but it will not update automatically.

View 1 Replies View Related







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