I'm having a problem with my macro .However, if i use the following script:
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.
'Private Sub Worksheet_SelectionChange(ByVal Target As Range) 'If Not Intersect(Target, Range("f6")) Is Nothing Or Target.Cells.Count > 1 Then 'Application.ScreenUpdating = False 'For Each c In Range("A1152:A1179") 'If c.Value Like "Hide" Then 'c.EntireRow.Hidden = True 'Else 'c.EntireRow.Hidden = False 'End If 'Next c 'Application.EnableEvents = True 'Application.ScreenUpdating = True 'ActiveSheet.Protect 'End If 'End Sub
The cell values in A1152:A1179 are derived from a simples If formula. I have tried to change the "Hide" to a zero value but this didn't make any difference
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.
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.
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.
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?
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?
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'.
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?
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.
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.
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.
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.
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........................
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.
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
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.
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.
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.
I want a macro to hid rows based on a specific value of a cell compared to another.
Example, In a cell I have the current date and time which updates when the the sheet is opened... On a row I have various info including another date in the past. When the date in the past goes over 6 months older than the current date, I want to hide that whole row.
Is this possible? Do I use an IF function in the macro I've seen to remove rows based on cells with specific values?