Macro For Hiding Rows Containing Empty Cells Through Different Columns
Sep 4, 2013
I 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
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 am trying to hide columns in a range, "P8:ET1087" but it isn't working. After I autofilter a value, every row will be hidden except for the rows where the value is found. This is always 6 rows, won't be more or less.
The 6 cells in every column are the same and contain from 1 to 6: Text Text Date Number Text Date
What I am trying to do is to hide the column if all cells in that column are blank/empty after it's autofiltered. That for the 135 columns, from P to ET.
How do I go about hiding empty rows in a separate linked sheet? To be more precise, I'm working on 2 sheets now. Sheet 1 would be the input sheet and sheet 2, the output sheet. For instance, there are 10 rows of data in Sheet 1, but out of this 10 rows, I planted data only into 4 rows. How do I ensure that Sheet 2 hides the empty rows (5th - 10th)?
automatically hiding empty rows on my table... though the table has been referenced via string of formulas which I added up on my worksheet.. Some of the rows on my table are empty and I tried a VBA code on automatically deleting the rows but then I realized that if that certain 'referenced' string of that cell is not empty anymore, that would cause me much trouble.. so basically I need a code that would automatically hide the rows that displays a result of "" and unhides when it is not empty.
Using the following code to remove empty rows based on whether a specific range of columns is empty. The code works if the cell has a zero, but not when the cell is blank. An example of the data is attached.
VB: Public Sub DelRows2() Dim Cel As Range, searchStr, FirstCell As String Dim searchRange As Range, DeleteRange As Range
I am looking for something that will first delete all empty columns up until it runs into data, then delete all rows that are blank in Column A until it runs into data. In other words, I recieve different spreadsheets that start their data in different locations and I would like to have them always start in cell A1.
I was wondering what would be the best way to go about hiding either rows or columns based on whether a particular cell is empty or not. The reason being, I want a graph and table to only display data when present.
I am trying to populate the 2 tables from excel to word. I will be getting the excel file with tables in various sheets. One sheet consist of 2 tables that will be inserted to one word document. So if there are 2 sheets then I will have the tables inserted in the 2 word document. In the excel sheet I have attached, there are 2 sheets with tables in each of them. I have written the code to copy and paste the table to word doc from (general) range A1:G4 (Table 1) and A9:H18 (Table 2) that has empty rows and columns selected. But there are empty rows and columns inserted since the table range is not same sheetwise. I would like get the empty rows and columns deleted in the word table.
Find the attached sample excel sheet and the word documents.
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 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.
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 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?
Iam an accoutant and have been given a task we have a workbook we use monthly that has about 50 columns, we have to go in and hide all the columns we do not need to print so I am wanting to create one that will hide the neccessary columsn print, unhide the columns and then hide the neccessary ones and print and so and so on.
I tried to do this by recording a macro and it sent me to visual basic and now says ithas all of these errors and i dont know how to fix them i need to do this porbabaly for about 20 different hiding and printing combinations. I am going to attach the errors i am getting when i try to debug in visual basic this is just for the first combination so i guess i need to know how to do debug for the rest of the combiantions. I am also hiding the entire column i dont know if i should be or not. I am attaching the file that i am doing the hising in as well. So like for the first i need to only keep column a,b,c (always unhidden) and then g for this time, print, unhide, then again column a,b,c active, then column u print and then unhide and on and on.
I am attempting to creating a Macro in excel with an offset feature but I am having trouble getting it to work correctly. I will include what I have created thus far, but this is what I would like it to do. There are 2 columns, A and B which are header columns for rows 2 through 75. Columns C through G contain values which are referenced in formulas in the corresponding cells in columns H through the end of the sheet. Ideally after every day I would like to run the macro to hide the next 6 columns which are showing. So on day 1 I want to run the macro to hide columns H through M. On Day 2 I would like the macro to offset and hide the columns N through S and so on. Each day an additional 6 columns (which is the all the data for the previous day) need to be hidden so that the current day's data is showing and can be easily read alongside the headings which flow vertically down columns A and B. If you could please help me with the proper form of this macro I would greatly appreciate it. This is the current macro I have made, I would like to add an offset command in there but am unsure which line to tie it to:
Sub HidePreviousDay() ' ' HidePreviousDay Macro ' Hide the Previous 6 Columns (the entire last day) ' ' Keyboard Shortcut: Ctrl+Shift+H ' Columns("H:M").Select Range("H2").Activate Selection.EntireColumn.Hidden = True End Sub
I'm referencing cells A25 and A26 as the range of columns I would like to hide in Sheet 2. I equated the cells in Sheet 1 to columns in Sheet 2. Unfortunately, the result was columns being hidden from column 1 to column x instead. I would greatly appreciate if anyone can kindly correct my macro. Also, may I ask how I can distribute the columns evenly after hiding/unhiding?
I've recorded a macro in which I unhide certain columns, copy and paste some information then hide those columns again. The problem is that when the macro is finished, it incorrectly hides columns K to AN. I did not record that and it's not in the code so I'm lost as to why it's happening.
Why it's hiding everything from K to AN. I've tried recording the macro several times but it's just not working, no matter the order in which I hide columns when recording it.
I have an elaborate spreadsheet that I have made for my company. I will give a little background how I have it set up: E6 contains a drop down menu of all products that we sell, and line items are populated based on the selection here. In columns N and O, I have a "Business Partner Costs" table that calculates a specific discount % based on the business partner.
Some of our products are sold to BPs with no discount. For these specific products, I am trying to get the BP Costs table to disappear, or hide.
Basically, I want something like: "=if(or(e6=ae2,e6=ae7,e6=ae12,e6=ae13,etc),hide columns N&O,show columns N&O)" in macro format.
I have a spreadsheet set with the days in sach column (eg 29-Jan-09, 30-Jan-09, 31 Jan-09, 1 Feb-09 etc). What I would like to do is hide all columns prior to a date which is entered into a cell/macro text box.
Does Excel have the ability to automatically hide rows without values in certain cells?
For example:
Sheet1
ABC1Route NumberTechnicianNumber of Units Cleaned25Tony237Don749 511Ray12613James16715Chuck21817
In this example, Rows 4 and 8 would automatically be hidden, leaving the other rows displaying. Of course, somehow I'd have to "Unhide" these rows at some point to add data if needed.
This is for a spreadsheet that is about 500 rows. Conceptually, I would automatically hide the rows w/o data in column B, analyze (or print) the worksheet, then "Unhide" the columns to enter data the next day.
Why does my macro mess up on second line C23 and not places Play Equipment or the amount in the right column it stays on row C22
[URL]....
Code:
Private Sub CommandButton1_Click() Dim SheetName As String SheetName = "Estimate1" SheetName = InputBox("enter the name of a sheet to use", "sheet name", SheetName)
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.
I have a worksheet with 40 colums and 9200 rows. There are columns with empty data in the rows. Can I have a macro to copy data in the rows in Column E, F, I, J K and L to empty rows below?
