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
Basicly I used conditional formatting and lots of "IF".My solution lacks in style and it's time consuming ..
Long story short: I need to modify the way some cells are displayed based on the selection in a specific dropdown list.
I need that whenI select Task3 from the dropdown list next to "step1: please select" , everything from row 15 to row21 (both 15 & 21 rows included) and from row 23 on, to disappear.
I would like a macro to look through a sheet.
1. Unhide all Rows
2. Check through all cells in Row, If all cell values in row is either 0 or Empty then hide this row.
I have a userform which manipulates data based on the userselection from the combo box. I have setup the userform so that the user may select up to 3 sheets due to the presence of 3 combos boxes. I need to writing an IF statement which checks to see if combobox1 is occupied to carry out a function, followed by it checking to see if combobox2 is occupied to carry out the function, and then checks to see if combobox3 is occupied and carries out the funciton.
As such, if only 1 combo box is occupied it would then only carry out the operation on combobox1's selection, and if none are occupied, nothing occurs, the box simply stays open. This is what I have so far, I know there is probably a more eligant way of writing such a If/Then/Else statement
Sub Start()
If UserForm1.ComboBox1.Value And UserForm1.ComboBox2.Value > 0 Then
Call Find1
Call kTest1
End If
If UserForm1.ComboBox3.Value And UserForm1.ComboBox4 > 0 Then
Call Find2
Call kTest2
End If................
I work for Local Governments where supervisors want me to create Project Update program.
I've created one with VBA but still, cannot get the unchose color circles in the same row to become blank.
Whenever they choose one of the 3 color circles, 2 becomes blank.
I hope this will explain well enough for you to understand
here's the code I've put in and with the attachment, you can see my explaination.
Sub TimeRed()
doit 3, "D"
End Sub
Sub TimeYellow()
doit 6, "E"
End Sub
Sub TimeGreen()
doit 4, "F"
End Sub
Sub BudRed()
doit 3, "H"
End Sub
Sub ComboBox1_Chg()
For Each Sheet In Worksheets
If Sheet. Name <> "CoverPage" And Sheet.Name <> Sheets("CoverPage").ComboBox1 Then
Sheet.Visible = False
Else: Sheet.Visible = True
End If
Next Sheet
End Sub
It works if I step through it (F8) but the ComboBox doesn't work. It's named ComboBox1, and in the properties the ListFillRange shows all of the names in the list in the ComboBox correctly.
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?
optimize the above Mcro to hide rows with "0".
Sub HideRowsw()
With Excel.Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
.EnableEvents = False
End With
[Code]...
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 RelatedI'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.
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 RelatedI 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.
Does anyone know a more efficient way to run this code?
View 7 Replies View RelatedI 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 RelatedI 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 RelatedI 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 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?
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
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'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.
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?
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.
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] .....
I have the following Macro that hides rows that contain old dates (in column B) in a range:
View 2 Replies View RelatedI'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.
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
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 RelatedI'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.
View 9 Replies View RelatedI 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
