I have values in column D & K. Now i want to hide the rows with Zero's if they are in both D & K. Now if there is a Value in K and a Zero in D i don't want to hide that row.
Could someone please help if this is possible. I attached the code below for hiding lines according to one column:
I have a large table with an unknown number of columns and an unknown number of rows. The table contains no formulas and no errors - only text, numeric values and empty cells. The top row contains text (headers).
Column I contains below the header only zeros and/or 1s. However, a few entries in Column I might also be empty cells. Same is true for columns I+4 (i.e., M), I+8 (i.e., Q), I+12 (i.e., U), etc.
How could I hide ALL such columns (i.e., I, M, Q, U, etc.) with a VBA macro?
How do I get the effect of hiding unused rows/column in the sheet, including headings, while the parts in use still display headings?
The pick below explains what I mean. [IMG][/IMG]
I have a macro that works great to hide rows with zeros in a specific column on one sheet. It is below:
Sub Hide_Rows()
Dim i As Integer
ActiveSheet.Unprotect Password:="Yourpassword"
Application. ScreenUpdating = False
Application.Calculation = xlCalculationManual
For i = 9 To 408
If Sheets("Tasks and times"). Range("H" & i).Value = 0 Then
Rows(i & ":" & i).EntireRow.Hidden = True
End If
Next i
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
ActiveSheet.Protect Password:="Yourpassword"
End Sub
My problem is that now I want to adapt it to also hide columns on another sheet in the same workbook (named OBS). This is because in the first sheet rows 9 through 408 are used for data but that range is divided into 25 sets of 16 rows. Each set corresponds to a column on the other sheet starting at column C. I would like the corresponding column to hide when the first row of each set (i.e. rows 9, 25, 41, etc.) are hidden.
I have a project that needs to be done today. What I'm trying to do is hide an area of a worksheet using a button click, then display that area when the button is clicked again. This area is approximately the lower right quadrant of the worksheet, so it shares column and row information with other data that must remain visible. Is there any way to do this?
Alternatively, I was thinking about making a duplicate of the worksheet, but omitting the area I want to hide on the duplicate sheet. The button would then hide and unhide the two worksheets, hopefully making it have the same effect as hiding or unhiding just that area. If I do this, I would need to maintain the exact same data in the visible area of the two sheets.
I have a pivot table full of data that needs a macro/vba/something to loop through it and hide all rows that have no fill color. The table had 210 rows so I want a quick way of hiding the irrelevant ones (only the ones filled in green or red), and so all the ones with no fill need to be hidden. Something like a button to hide/unhode would be ideal.
View 4 Replies View RelatedI am trying to hide all rows and columns with zero balance in the cells with the use of a macro button.
View 8 Replies View RelatedI'm running a macro with multiple statements for hiding columns, and it has been running well for years, now today i'm getting a error message in the macro while debugging that states "Unable to set the Hidden property of the Range class", and when I reset the macro, and try to manually hide the range, I get the error message "Cannot shift objects off sheet".
View 2 Replies View RelatedI'm trying to hide groups of columns on a dropdown change. I'm extremely new to VBA, so I'm having a bit of trouble understanding some things. Here's what I've got so far...
Code:
Private Sub modeList_Change()
Dim selectedMode As Integer
Dim selectedOpp As Integer
[Code]...
I recognize my main problem is the assigning the multiple ranges to be hidden to the leadColsArray.
I am using an area of 43 Rows by ~ 150 Columns to display the results of an advancedfilter process. I have a trigger on row 44 for each column (which is either 1 or 0, depending on if any of the rows in the column were filled with data. If any column is completely empty I would like to hide it. I am getting an error if the majority of the ~ 150 columns are empty and should be hidden. Does anyone know if there is a maximum number of columns that can be hidden each time a macro is run?
My piece of code to accomplish this is as follows:
For i = 4 To 150
If Worksheets("RegMatrix"). Cells(44, i) = 1 Then
Worksheets("RegMatrix").Columns(i).AutoFit
Else
Worksheets("RegMatrix").Columns(i).ColumnWidth = 1
Worksheets("RegMatrix").Columns(i).Hidden = True
On Error Resume Next
End If
Next
Right now, once i gets to about 100 the rest of the columns are not hidden.
I like the look of when only the used columns and rows are shown. I like to hide all unused columns and rows, and have the background and a minimalist spreadsheet.
HOWEVER, is it just me, or does Excel move a lot slower when thousands of rows and columns are hidden? Particularly, opening files seems to be slower. I'd love to delete them entirely from existence, so Excel only has a few rows and columns to work with, but that doesn't seem to be an option.
I would love to be able to limit the number of rows in a specific sheet so that I can quickly carry formulas to the bottom of a worksheet without carrying them to row 1,048,576. I'm aware of options to hide rows and/or to limit the scrolling with the "view code" option on the worksheet tab; however, I'm mainly worried about worksheet performance. When I carry a formula down, I don't want to see a spinning blue circle run for minutes at a time, and I'm hoping to avoid the same spinning circle when I undo my formula. Is there any way to actually limit the number of rows, or is there any other limitation I could put in that would increase worksheet performance?
View 1 Replies View RelatedI created a macro that analyzes some datasets and according to different parameters it puts an X at the side of every cell that goes out of parameters. These Xs are all in columns named the same way for it to be easier to find. What I want to do is to write some macro that will hide every row where the data did not go out of parameters to be able to easily see where it did. In the following example it would need to hide the second row since there are no Xs in that row. One of the problems I'm having is that the amount of columns and rows is variable.
Data
Data
Functional Upset
Data
Data
Functional Upset
#
#
X
#
#
[Code] .......
Below is the piece of code that I created to try to do this (G has the value for the amount of rows). I think it is working but it is either taking too long since it has to sometimes go through over 20k lines as much times as it needs to to cover all the rows that contain Xs or its getting stuck for some reason.
Code:
Worksheets(2).Range("I3:I" & G).EntireRow.Hidden = True
K = 0
Do Until Worksheets(2).Range("I1").Offset(0, K) = ""
If Worksheets(2).Range("I1").Offset(0, K) = "Functional Upset" Then
[Code] ......
I have a user form with 4 list boxes that I'd each like to populate with their own lists of cities according to time zone - one list box for each time zone (ET, CT, MT and PT).
I attached a worksheet that has some code from another project I got from another thread on this forum, but it needs to be modified (or whole new code written) for this application, including initializing the control buttons.
The code I'm borrowing has a slight glitch in it, where if you select the last item in the list box to hide, that item will be missing from the list the next time the user form is opened, and you'll then have to manually unhide that column.
I've tried several approaches on how to code this, but can't figure out how to work it. I have a large spreadsheet; which dynamically changes in both column and row counts. So, I have an array of values. If any cell has a negative value, that cell text needs to be changed to red AND that row needs to remain displayed. If ALL values in that row are >=0 then the row can be hidden.
The array is actually a pivot table named QTD
For Example: If cell D5 = 5 and F5 = -3 then Row 5 should remain displayed.
If Cell D6 = 5 and F6=0 then row 6 should be hidden because all values are > 0
Then I was thinking of using the case statement to hide the rows but can't figure the syntax. I'm open to any other means of performing the taks as long as the initial array dynamically to encompass all the data.
I am trying to find code that will allow me to hide a set number of rows based on the value of a specific cell which I need to work for two worksheets in the same workbook. Is that even possible?
For example: when i enter 5 into cell D1, I need five rows to be visible on both sheets.
I have created a spreadsheet to show some reports and I wanted to serch for some datas which overloops themeselves. If you can have a look at a test file I attached you will see the full picture. I have 2 tables, where the 2nd one is on the right side of the 1st one. 1st table:..............
View 3 Replies View RelatedI need the values that are copied from the template to copy over in text form from the "Data" Tab. Secondly, the master sheet has multiple lines for each vendor. For the area highlighted in red I'd like for it to copy all cells in column C for the vendor and search the vendor by name. Then, move to the next sheet.
View 10 Replies View RelatedI want to to copy selected columns of sales data into rows organized by salesperson. I have just started out with VBA and find that I cannot do it myself.
My original data are in the form of the following:
invoice_no
product
sales
qty
total
[Code] .....
I want to display the data in another sheet in the following format:
sales_a
sales_b
sales_c
sales_d
[Code] ...........
1. Copy data from original file (I do not want to do anything in the original file) into the spreadsheet (Target worksheet)where the code should run.
2. In sheet 1 of Target Worksheet, there are 2 columns which I need to set criterias on namely Column D and Column L
3. In Column D, I want to specify 3 criterias namely A, B and C
4. In Column L, I want to specify 5 criterias namely London, Frankfurt, New York, Sydney and Tokyo
5. If Criterias in 3 and 4 are met, copy all rows into Sheet 2 of Target Worksheet
I need to find all rows that have columns that match in all 3 of the columns.
I then need to delete all but the last row in each "group" of rows.
like:
1 q w e
2 q w r
3 q w r
4 q w r
Delete rows 2+3
The first code hides everything just fine based on the date in A1. When I change it to the second code to delete instead of hide it is leaving a bunch of rows that the 1st code hides. Both codes have the same search criteria.
Code:
For Each cell In Range("B8:B5000") If cell.Value Range("A1").Value Then cell.EntireRow.Hidden = True
Next cell
Code:
For Each cell In Range("B8:B5000") If cell.Value Range("A1").Value Then cell.EntireRow.Delete
Next cell
I have a large spreadsheet that contains and employee number and then up to 5 entries of Dependants (each has its own column). What I need to do is have 1 for each of the dependant information instead of 5 and have multiple rows for the employee if they have more than one dependant.
At the moment I am sorting the data by dep 1 name, pasting the data into a new sheet, deleting all the dep 1 stuff, sorting by dep name 2 and pasting that.........etc.
It doesn’t take too long to do but I just wondered if there might be a quicker way as this will be something I will need to do more often.
create a formula,for multiple criteria with multiple results in rows.
[URL]
I have a sheet that is organised as follows:
a) Column A contains 50 names, all unique. R1C1 heading is "Name".
b) Column B to Column H are for some text that can contain special characters, numbers, spaces etc.
c) R1C2 heading: "Day 1"
d) R1C3 heading: "Day 2". and so on till R1C8 having heading as "Day 7"
e) All the cells are filled in the specified range as specified in (b) above.
In summary, each of the names will have 7 different tasks associated with it in the row. These tasks can repeat for all the names.
For example: Consider the below set of data for 3 rows and 7 columns (spaces are used as column delimiter. In reality, spaces can form part of the tasks for each name):
Name D1 D2 D3 D4 D5 D6 D7
-------------------------------------
aaaa T1 T1 T1 T2 T3 T4 T5
bbbb T1 T2 T3 T3 T3 T4 T5
cccc T1 T4 T4 T4 T4 T4 T4
I hope its clear till this point.
Now my requirements are as follows:
a) Find out all unique elements from the set of tasks across all days and list them from 100th row onwards with each unique entry in new row.
b) For each of the unique tasks, list the names that have worked on that task in the same row.
For example, the output should be as follows:
T1 | aaaa, bbbb, cccc
T2 | aaaa, bbbb
T3 | aaaa, bbbb
T4 | aaaa, bbbb, cccc
T5 | aaaa, bbbb
The character "|" above is used as a seperator for columns.
Date
Name
Diesl
Petrol
Oil
Kerosene
[Code]...
Here from the above table i want to get the complete sum of all numbers corresponding to a specific date and person..
I used sumifs which looks like this: =SUMIFS(C2:F10,B1:B10,A12,A1:A10,B12)
But that doesn't work, should i resort to using multiple sumifs?
Here's what I'm trying to do -
There's a sheet called "Main" and 38 other sheets that shows the data (these 38 sheets all have the exact same structure/layout). Let's say these 38 sheets are called "country1", "country2", .... "country38".
Based on user input in cell J10 in "Main", I want to show only the relevant columns in all of the 38 country sheets. Specifically,
If J10 in sheet "Main" = "Option 1", hide all columns in all 38 sheets except columns A to W.
If J10 in sheet "Main" = "Option 2", hide all columns in all 38 sheets except columns Y to AU.
If J10 in sheet "Main" = "Option 3", hide all columns in all 38 sheets except columns AW to BS.
If J10 in sheet "Main" = "Option 4", hide all columns in all 38 sheets except columns BV to CQ.
If J10 sheet "Main = "ShowAll", show all columns in all 38 sheets....
Cell J10 in "Main" will be a drop down bar with the 5 choices.....
I have a spreadsheet with dates runnung from column D:IV,
I am running a simple macro but need this macro to hide columns D to whichever column is yesterday's date.
I have a spreadsheet that I'm trying to hide specific columns. In Row 6 I have the day of the week (Sun thru Sat) and Row 7 with the respective date starting in column F to column ZZ.
I would like to hide all columns with Saturday and Sunday in row 6. I tried using VBA but I'm new to coding and can't figure out the correct syntax.
I was wondering if there was a simple function that will hide all the columns which have Zero value thoughout a pricing spreadsheet.
The different elements of the pricing have lead to 60+ columns, upto 75% may not contain a value or may display "false", it is making it very difficult to view and print. At present I am manually auto filterig to see if values are present in the column and then hiding the columns if they are blank (a real pain in the backside).