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 RepliesI want to write a macro to fill color in row 8 of the worksheet as solid black . Also columns P,T,AP,BC ,BE and BG should be filled solid black and width should be 1 for the row and the column.
View 7 Replies View RelatedI am a locksmith and attempting to write a spreadsheet app. for my line of work. The output page will use columns A-G and rows 1-?10000? depending on the size of the system. If a 5 pin system is requested then columns A+B will be blacked out. 6 pin system A is blacked out. 7 pin system nothing blacked out. Also, if any cell matches the master key that was input, that cell is filled with red. I know, confusing.
Basically, I am trying to remove the blacked out columns automatically, and to remove the enitire row is any cell in that row is filled with red. Then to remove blank lines.
1. By entering a numerical value in Cell A1 for example typing 5.
2. Then, next five columns would be blocked with specific color.
3. if i am typing 10, then 10 columns would be blocked with specific color.
how I can alternate the background color (fill color) of rows in a spreadsheet. Say I wanted every other row to be gray starting at row 10.
View 6 Replies View RelatedHow 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.
is there any easy way to fill alternate rows of a worksheet with a particular color? I have a worksheet with 175 rows and alternate rows are to be filled with green!
View 4 Replies View RelatedI 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 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 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 found a link to my problem in the subject title below,
http://www.ozgrid.com/forum/showthre...t=45580&page=2
The guy at the end of the post says he got it working in the end, but no one seems to have posted the final solution - could someone tell me if the solution is actually in the thread, and if so where!
My problem is outlined by this guy - basically i want to be able to delete rows in a range that have no fill color, and so keep the ones that are colored.
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] ......
Is there a way to color fill an entire row based on a value in a certain column? Say I have a large file and one column is "yes" or "no" If I wanted all of the "yes" rows to be colored -
View 5 Replies View RelatedI have a simple spreadsheet. A column for a persons name and 31 columns, one for each day of the month. I want to apply conditional formatting, either fill color or border color, to the date column of the current date when the spreadsheet is opened.
View 2 Replies View RelatedI would like to automatically color fill a series of rows based on like information from a single column. When the information in the column changes then the rows would either stop filling (this would be best) or fill with a different color until the information changes again.
Summary: series of alternating rows would either be filled or unfilled based on changes from the column information.
Hope this isn't too confusing. Below is an example except I would want the cells filled, not the text or numbers to change. The highlight is changing based on the changing of the numbers.
ABCD 12345
ABD 12345
ABCD 12349
ABDF 12349
ABCD 12358
ABF 12358
I have 4 columns. I want to add 100 to all rows in the first column, 150 to the ones in the second, etc. Is this possible? I have Excel 2007.
View 9 Replies View RelatedI have a table = Columns A - E contain data in a variety of formats
Code1
Code2
Code3
Code 4
Code 5
Code6
dog
9
12/1/1987
hamster
dog,9,,12/1/1987,hamster
What Column F (code 6) should contain is a csv of Columns A-E as shown.
What formula for Column F will automatically fill the values for all data within a row in a csv?
Data file with few columns. There are groups of similar ID numbers in Column J. For a group of similar ID numbers in consecutive rows there is only one row that has a number greater than 0 in its Column L cell and the rest of the cells of Column L for that set of similar IDs is filled with 0s.
First for that unique ID group I need to find out which row is it that has a value greater than zero in its Column L cell.
Then I need to use that value to fill the rest of the 0s in Column L corresponding to that set of Unique IDs.
The process continues with identifying similar IDs in Column J and this time doing the same thing for their Column M. I have attached a sample file that shows the data and how the results need to look like.
See here Fill Cells.xlsx
I want to fill an array from values in range A1:H10. I want to fill the array with all rows in range and only columns B,C and E. I have the code below so far using index function.
Is there a more direct way to select all rows from desired range to avoid the need to create an array of rows from 1 to LastRow and then use Application.Transpose(RowsArr) (in red) inside Index()?
I am trying to fill in reference cells horizontally but fix the column and increasing the row number as it fills. For examples I want to start my refence as "=A1" in cell B1. I want to copy this reference horizontally to column Z1 but want the refences to be A1, A2,...,A26. Excel only seems to increase columns when filling horizontally and rows when filling vertically. Transpose works but it's an array which would slow the spreadsheet down.
View 4 Replies View RelatedI'm trying to add a textbox at the current position (selected cell) with a set size, fill color, and border color. I found this: http://msdn.microsoft.com/en-us/libr...8(VS.80).aspx:
View 2 Replies View RelatedI'm running a macro that opens another workbook and read data from it.How can I incorporate this code into my macro.Sorry i don't knwo VBA.
Workbooks.Open Filename:="C:Documents and SettingsmsimantbDesktopINFRACHEM_POLYMERS - DON''T DELETE.xls]Sheet1"
UserGRP_MAcro Macro
Rows("1:3").Select
Selection.Delete Shift:=xlUp
Columns("A:B").Select
Selection.Delete Shift:=xlToLeft
Columns("B:E").Select
Selection.Delete Shift:=xlToLeft
Columns("A:A").EntireColumn.AutoFit
Rows("2:2").Select
Selection.Delete Shift:=xlUp
Range("B1").Select
ActiveCell.FormulaR1C1 = "Existing userGroup"............................
For some reason the fill color and font color no longer work! If I set the font color as red, it continues to show up as black!
This is the strangest thing! I have no conditional formatting set and this just started a week ago.
I want every even row that has a value in it to have a different fill color (same color for all). how to do it? I am using 2007.
View 5 Replies View RelatedThe 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 am trying to color code certain rows in my data. there are only two columns a, column b. my code below will look at some row number in column 1 and look for a value "total", then again, look on the same row number, column 2 and look for value "1", then color fill it.
View 2 Replies View RelatedI have a vba script, that imports data from a sql server and queries it in excel.
My question is, Could someone tell me how I could tell excel to color fill rows that if 3 different columns did not match, color the rows that the data is on ,red. ex:
row1 : column b2 =5, column c2=5, column d2=6 , row1 would not be red.
But if
row2 :Column b3=5 , column c3=6 , column d3=7 , row2 would be red .