Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










Hide All Unused Rows And Columns


Is there a simple way to hide rows and columns? A lady in my office just asked me how to make only Columns A to J visible and only show 100 rows. She doesn't want the rest of the spreadsheet to show. I know how to manually hide columns and rows but it takes a while to drag down and select 64000 rows so I thoguht I would ask the question:

Does Excel have a feature that will automatically hide all unused rows and columns or is there a simple VBA code to do this?


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Button To Hide Unused Rows
trying to create a button to hide unsed rows, can anyone help? basically the sample below is what i want to do but it hides all the rows listed.. i want it to hide only unused rows in the rows selected if yes is selected and unhide them when no is selected...

wannahide = MsgBox("hide rows", vbYesNo)
If wannahide = vbYes Then
Rows("5:28").Hidden = True
Else
Rows("5:28").Hidden = False
End If

View Replies!   View Related
Removing/Greying Out Unused/unwanted Columns & Rows
Need to find out how I can restrict a worksheet to show only the columns and rows that I want. For example, I want to show columns A to H, and rows 1 to 25, and nothing else - I don't want there to be a column G, or a row 26, just blank grey space. I know it's possible, because I've seen it done : D . But the closest I can get to is: View > Page Break Preview, which isn't quite what I'm after.

View Replies!   View Related
Conditional Formatting - Unused Columns
Is it possible to use conditional formatting to set all the unused columns (S - ZZ) interior color to gray?

View Replies!   View Related
To Delete My Unused Rows
Apparently Excel can't delete unused rows, nor can it specify the sheet to be exactly X by Y large. My problem comes in any time I fill down a column of data by selecting the column header (A for example) and selecting fill down, rather than selecting the exact range of cels I need the formula in (which might be several thousand...) Excel winds up taking my sheet that was, let's say, 1000 rows long and filling down until now it's many many many thousands of rows long, with "error" fills in most of the rows. This now means that any kind of sorting, resizing of rows, etc. takes absolutely forever because it's dealing with 5 or 10 times more rows than I am actually using. Even clearing the contents of all the extra cels does not help. I can't in any way seem to get rid of these extra rows.

View Replies!   View Related
To Delete Rows Unused
I have some if statements for 300+ employees. Some rows are left blank b/c a specific type of employee didn't meet the if statement criteria...which is what I wanted. Now how can I get rid of those blank rows (that actually do contain formulas but are returnnig a "" value) to have all of the employees in a "nice" grouping?

View Replies!   View Related
Delete Unused Rows
I have a page full of data and have some rows in between them which are empty. Is there any way that I can delete the unused rows through VBA code.

View Replies!   View Related
Hide Rows With 0 In X Columns
I have a workbook comprising several worksheets and on each of those worksheets I want to hide the rows where there is a zero in each of three specific columns on any row. If there is an entry that is more than zero in any one or more of those columns then the row should not be hidden.

In the attached example I would want to hide rows 4, 8, 12, 18 and 19 as there was a zero in each of the columns C, E and F. I would not want to hide the other rows as they have a figure that does not equal zero in at least one of the columns C, E or F in the row.

a macro for making it work on just one sheet - I can write the code to make it work for each of the worksheets in the book.

View Replies!   View Related
How To Hide Rows Based On The Value Of Two Columns
I need to filter out rows, based on a specific value in column A, (documents on file for the clients), then check column C (last name) and D (client first name), which can have the same client listed multiple times, based on how many different documents are on file ......

View Replies!   View Related
Macro To Hide Rows And Columns
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


View Replies!   View Related
Hide Cells, Not Rows/Columns
Is it possible to hide just a portion of a column? I want to hide just E85:E98, for example. Not all of column E.

View Replies!   View Related
Hide Combo Box With Rows Or Columns
I have created a macro which hides columns I:O on a worksheet. Within this area I have added a combo box - but when I hide columns I:O (via the Macro) the combo box remains visible. Is there a way of ensuring the combo box is hidden along with the selected columns?

View Replies!   View Related
Form Control To Grey Out Unused Rows
Is there a macro that will format unused rows or with no value in a given range. I have a sheet that I am using that some of the date is either added or deleted and would like to have a macro to do grey out unused rows. I am using this with a form control.

View Replies!   View Related
Removal Of Unused Rows (variable Range)
I have a spreadsheet ranging from A1 to AH602 Column AH contains row totals and Row 602 contains colunm totals. I am importing a CVS file starting at cell A4 - the number of rows contained in the import varies drastically. I would like to build a macro that would remove all the unused rows. I have tried to record a macro that would do this but the number of rows always ends up static and not variable.

What I'm doing below is Using an END+HOME to take me to the furthest point in the active range then 3 UP ARROWS to get to the desired row then an END+LEFT ARROW followed by END+UP ARROW and 2 DOWN ARROWS to get to the starting cell in Column A. At that point I use a SHIFT+END+DOWN ARROW to capture the range of Rows I need to delete.

Sub Macro1()
ActiveCell.SpecialCells(xlLastCell).Select
Range("AH599").Select
Selection.End(xlToLeft).Select
Selection.End(xlUp).Select
Range("A101").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.EntireRow.Delete
End Sub

This works except that the starting cell remains the static.

View Replies!   View Related
Combining Hide Columns With Autofilter Rows
I have a spreadsheet in Excel 7 for a lifelong cashflow model. The main worksheet is called 'Projection' with ages in rows & income (from a variety of sources) and expenditure (of various kinds) in columns fed with data from the Input sheet.

On the 'Input' sheet I have a button called Format Graphs which controls the following macro:

View Replies!   View Related
Hide Rows In VBA Macro By Checking 2 Columns
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........................

View Replies!   View Related
Hide Blank Rows Which Spans Two Columns
Hide Multiple rows. I have text within two columns A, D. How can I Hide Blank rows which spans two column Named Ranges, "Range1" (A1:A15) "Range2" (D1:D15). As an example:

Beginning with this:

...A......D

1.Text
2........Text
3.
4.
5.
6.Text
7.Text
8.
9.
10.......Text
11.Text
12.
13.......Text
14.
15.......Text

To this , after hiding blanks:
...A......D

1.Text
2........Text
6.Text
7.Text
10.......Text
11.Text
13.......Text
15.......Text

View Replies!   View Related
Hide A Rows/columns Based On A Cells Contents
way to automatically hide a row or column based on a specified cell.

Or even something general like: Hide all rows with cells evaluating to #n/a.

View Replies!   View Related
VBA Macro To Filter Rows By Cell Value & Hide Blank Columns
i have created a spreadsheet to simplify our work flow, I am stuck on what is probably the easiest of the commands.

basically have rows dedicated to specific codes and the colums represent values relating to each code, all codes have a different set of values, the attached example only has a few variables but the actual worksheet will have several hundred.

the idea is the user will input the code they wish to get details on in A2 and then press the command button and it will then show (as per the after sheet in the attachment) just the relevant information for that code, so filter the code in column A and hide the columns which hold no value.

where i am getting stuck is I am not sure the best way to proceed, is it best to create the macro button to do the filter and hide or is there a better way using vlookup and a pop up window asking for the relevantcode to be inputted to to retrive the information, again understand there will be hundreds of colums and hundreds of rows and the values may be 20 or 30 colums apart for some of the Codes so this simplification is really saving the user a lot of time.

View Replies!   View Related
Hide Rows Based On Multiple Columns Formula Returning Empty Text ""
I would like to rows based on multiple column conditions criteria. ie., if the columns N, O, P values are "", then hide the particular row. The logic given in the website here, i tried But, it is not 100% working. It works for a few rows at the start of the database & it works for the rows at the end of the database. In between, for a few rows, even if the column values are "" it does not hide those rows.

View Replies!   View Related
Hide Columns & Hide X-axis Labels
I am filtering the data displayed in a chart by hiding columns. I would also like to filter the X-Axis labels by hiding columns. If I do this manually I have no problems but when I run the following macro the chart gives a reference error for the X-axis labels.

Sub ShowA2()
Application. ScreenUpdating = False
num = Sheets.Count
Sheets("X-Axis").Activate
Range(Columns(1), Columns(256)).Select
Selection.EntireColumn.Hidden = False
For a = 1 To 5
Sheets(num - a).Activate
If ActiveSheet.Name = "A2 Data" Then
Columns("A:Q").Select
Range("A10").Activate
Selection.EntireColumn.Hidden = False
Sheets("X-Axis").Activate
Columns("A:E").Select......................

View Replies!   View Related
Hide Blank Rows Based On Values In Other Rows
I have a worksheet used for inventory. In Column A is the quantity (to be entered manually). In Column B is the product description. In Column C is the price of the product, and Column D the total price (column C price x the quantity entered in Column A). At the bottom of the worksheet is a grand total. Also, Column B (products) is grouped into subheadings by the supplier each product came from (for example, row 6 has the title PPG, and then rows 7-137 list every product from PPG).

The calculations in this worksheet work fine. What I am trying to do is, using a macro once all of the appropriate quantities are entered in column A, automatically hide every row of product that does not have a quantity. The tricky part is, if no products under a given supplier subheader are entered, the subheader also hides, and if a quantity is entered, that subheader shows. For example, if I have no quantities under any products for PPG, then the PPG subheader hides, but if just one quantity is added, PPG shows. Also, this list will be constantly updated, new products will be put in and taken out all of the time, so I cannot base the macro on a specific number of rows.

View Replies!   View Related
Populate With The Next Unused Number
I am trying to create a Purchase Order Form that will automatically pull the next unused number from a summary Purchase Order log. I would like to create a formula that will look for the next blank cell in Column B and then pull the number from Column A in the same row.

View Replies!   View Related
Hide Various Columns
I have a very large spreadsheet that I work in and then print from. When I print, I need to hide about 20 columns which I do manually. I am curious if there is a way I can do this easier. If I could somehow filter columns, or set mutliple columns to a name for a dopr dowm box, or possibly a macro.

View Replies!   View Related
Hide Columns Where Row X Has Zero
I have found vba to hide rows that contain zero values, but not columns.

View Replies!   View Related
Hide Columns
I am trying to use an IF formula to do the following for each row in a large spreadsheet: If the value in column W = 0, show columns A,B,C and E. I have one formula I've used that doesn't work, but as posting what I think is the correct answer could get my post deleted, I won't put it here.

View Replies!   View Related
Hiding Unused Worksheet Space
I've just finished my project (with loads of help from you guys) and wondered if there was any way of making it look less like the spreadsheet it is when it's opened.

I've removed everything I can and I'm left with a vertical scroll bar (needed) and the top menu bar (not needed).

Is that about it or can I get rid of that, and maybe size the window accordingly when the workbook is opened (so the dead space on the right is not seen) and perhaps stop the vertical scrolling at the bottom of the sheet so it doesn't scroll down to 65555 and the dead space under the sheet isn't seen.


View Replies!   View Related
Hide Columns With VBA When Cell Value Changes
I have a list of 24 columns (12 months each for forecast and actual data). I want to put VBA code in the worksheet that will hide a column based on a cell value in each column. On line 4 there is a formula that returns either "show" or "no". The columns are AS:BP. The code needs to run on a change in any of the cells AS4:BP4.

View Replies!   View Related
Hide Columns On Opening - IF
The other day I had posted a thread asking if anyone could help me ensure certain columns were hidden when a .xls was opened.
Here-> http://www.mrexcel.com/forum/showthread.php?t=312938

Now Id like to be able to hide the columns only if row B of the colum contains the text "hide me". Please not the cell may contain other text, such as "Q1, hide me"

Anyone know if its possible?

eg I want to hide Cols B and D

1ABCD2Shop items3dateQ1, hide meItemSupplier, hide me401/01/2006q1 2006JuicePepsi505/01/2008q1 2008crispswalkers

View Replies!   View Related
Hide Columns With No Data ...
I have a sheet that shows multiple part numbers as the headings for the columns. The rows show each individual job/install. If there is an "x" in the column, then the part is needed and must be pulled by the warehouse. If there are no x's at all in the column, I would like the column hidden so that I can print out a concise "pick list" for the warehouse that only shows those items needed.

View Replies!   View Related
Hide Columns Via A Macro ...
I have created this macro (below) in a standalone spreadsheet and the expected results are that Columns A,B,C,D,G,H will be displayed after I run the macro.

But when I use the same macro in my production worksheet (columns and ranges adjusted accordingly) this macro creates the following results: Column A is displayed and all the rest are hidden (B,C,D,E,F,G,H). I am stumped as to why this occurs. Can you advice me as to how to get this macro to work and display A,B,C,D,G,H ?

View Replies!   View Related
Hide Two Columns If One Is Blank
Currently using this code to search Column D and hide if the cells are blank. How would I go about also hiding Column C as well, If D is blank?

I am using the spreadsheet as a grading template. The assignment name is under column C and the grade under column D. If there is no grade then I would like to be able to hide the unused assignment column along with the empty grade column.

Sub HideColumnsInd()
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
Dim rCell As Range
For Each rCell In Range("D3:D48")
If rCell = "" Then
rCell.EntireColumn.Hidden = True
Else
rCell.EntireColumn.Hidden = False
End If
Next rCell
Application.EnableEvents = True
End Sub


View Replies!   View Related
Hide Certain Columns In The Workbook
My goal is to be able to hide certain columns in the workbook based upon certain criteria. Moreover, I need to be able to hide columns in a certain tab even if I working in a different tab (i.e. if I am in Tab "A", I want to be able to hit "Ctrl + Alt + X" and be able to hide columns in Tab "B").

In order to accomplish this, I selected the Visual Basic icon. I then clicked on the "Insert" drop down menu and selected "Module". I have included a sample of the code that I wrote and inserted in to the module (see below). This general pattern of code repeats itself for 16 different macros - all in this same module.

Sub Macro01()
Worksheets("Input - Historical Financials").Columns("A:AD").Hidden = False
Worksheets("Input - Historical Financials").Columns("D:AD").Hidden = True

End Sub

Sub Macro02()
Worksheets("Input - Historical Financials").Columns("A:AD").Hidden = False
Worksheets("Input - Historical Financials").Columns("F:AD").Hidden = True

End Sub

View Replies!   View Related
VBA To Hide Columns On Second Sheet
I'm trying to hide columns M:R on sheet "1up" if cell N4 on sheet "Fill In" is blank and unhide those columns if N4 contains an "X". Columns AK:AP should also hide or unhide on "1up" if AD4 is blank or not on "Fill In".

I've pieced together the below code by recording macros and piecing together code I've found in other threads on here. I placed the code in Sheet 1 (Fill In).

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("N4").Valuse = "X" Then
Sheets("1up").Select
Columns("M:R").Select
Range("M7").Activate
Selection.EntireColumn.Hidden = True
End If
Sheets("Fill In").Select
Range("N4").Activate
If Range("N4").Valuse = "" Then
Sheets("1up").Select
Columns("M:R").Select
Range("M7").Activate
Selection.EntireColumn.Hidden = False
End If
Sheets("Fill In").Select
Range("N4").Activate
End Sub

View Replies!   View Related
Dynamically Hide Columns
I am using drop-down lists in a cell to filter out some data in a spreadsheet. Works great, but I would like to hide certain columns depending on which item is selected in the drop-down list. I have written a macro to do this, but I cannot get the macro to run automatically every time there is a change in the drop-down list.

View Replies!   View Related
Hide/Unhide The Columns
I've come across a spreadsheet where certain rows and columns (typically top rows or left columns) are hidden; however, there's no way to unhide them (the unhide function is grayed out) and it doesn't seem to be protected or no visible macros/vba on the file.

View Replies!   View Related
Hide Unsequential Columns In Vba
I'm using Excel 2003 and want to hide columns D:E & I:J. I have script as follows which hides I:J but I am unsure how to get it to include the other 2 columns.

View Replies!   View Related
Hide/Unhide Columns
I have a spreadsheet that has 28 columns for time entries. Typically only the first 12 columns are used, so I would like to hide the remaining 16 columns (which makes the spreadsheet much more user-friendly). It would probably be nearly impossible to teach all of them how to Unhide the remaining columns (and re-Hide), plus I would like to use the full-screen function when employees enter thier times. I would like to use a form control in the column heading so that when the employees 'check' it, it will Unhide and then re-Hide the columns. Any way to do this? Seems like a VB thing to me (out of my league, but would be happy to add one in!).

View Replies!   View Related
Hide Columns If Cell In Row Is Zero
I need a macro to go through and look at each column between and including E to AW. If it sees a 0 in the cell in row 1 then I want it to hide that column completely and go to the next column.

View Replies!   View Related
Conditionally Hide Columns
I am currently using the following code to hide each row when there is no data between a certain column range. I need to add to this code so that it will also hide each column when there is no date between the row range.

Private Sub Worksheet_Activate()
Dim HiddenRow&, RowRange As Range, RowRangeValue&
ActiveWindow.DisplayZeros = False
Application. ScreenUpdating = False
For HiddenRow = FirstRow To LastRow
Set RowRange = Range(FirstCol & HiddenRow & _
":" & LastCol & HiddenRow)
RowRangeValue = Application.Sum(RowRange.Value)
If RowRangeValue <> 0 Then
'there's something in this row - don't hide
Rows(HiddenRow).EntireRow.Hidden = False
Else
'there's nothing in this row yet - hide it
Rows(HiddenRow).EntireRow.Hidden = True
End If
Next HiddenRow
Application.ScreenUpdating = True
End Sub

View Replies!   View Related
Hide Columns With Zero In 1st Cell
Im wondering is ther any possibility to filter colums. My sheet is having column headings as 1's and 0's .Columns with '0' as heading are to be hidden ..can i add any filter.

View Replies!   View Related
Hide PivotTable Columns
Hide values in Column layout on pivot table i only want to show th total value as its based on quarter figures not per month and this way it looks like Quarterly totals are broken down per month and has to have 200 a mont whenits in fact 200 for the complete quarter, how can i had the 200 for each of the month and just have the total per country? This is what my pivot table looks like

county Month Quarterly totals

UK June 200
july 200
August 200

Uk Total 200

View Replies!   View Related
Automatically Hide X Columns
I am trying to let Excel automatically hide unneeded columns. A brief description:
In this sheet one should enter a number of "projects" into cell DW7 (7,127). The maximum number of projects is 60. Each project consists of 2 columns (with already prepared formats and functions). The projects are numbered from 1 to 60 (the actual project number is in Cell G6 (6,7), I6 (6,9)...).
For this purpose I wrote the following

Sub worksheet_calculate() 'Hide columns that are not needed
Application. ScreenUpdating = False
Dim i As Integer
Dim r As Integer
i = 1
For r = 6 To 126
If Cells(6, r + i) > Cells(7, 127) Then
Columns(r).EntireColumn.Hidden = True
End If

If Cells(6, r + i) <= Cells(7, 127) Then
Columns(r).EntireColumn.Hidden = False
End If..................

View Replies!   View Related
Hide Columns By Date
I have a spreadsheet that is updated monthly. THe spreadsheet has a column for each month of the year, plus other columns. I would only like to display the current month and all past months - with the future months being hid from view. SO each time the user opened the file all headers with future dates will be hidden from view. I only would like to see the past months and other other no date column information. Is this possible to do in excel?

View Replies!   View Related
Hide/Display Selected Columns
My boss wants me to create a macro to hide columns if there is a "X" on top of the column labels. The macro should be in toggle mode whereby the next pressing of the macro will unhide the hidden column.

There are more than 50 columns in my actual worksheet. The macro has to check column by column whether there is "X" marked on top and hide it if so.

The next pressing of the macro should then unhide all the hidden columns.

View Replies!   View Related
Hide Columns With A Click On A Button
I want to be able to have a set of buttons or a dropdown menu where i can put all the titles of my columns in.

After clicking on one of the titles the column must hide or show itself, is there a way to do this?

It's for an automated sheet wich contains a lot of information and this will be used by a lot of people, i want to make it easy for people to simply generate a sheet with just the info they need.

View Replies!   View Related
Hide Columns Based On Selection
I have a worksheet that lists the months of the year on row 3 from columns C through N.
What I am trying to do is to be able to select the month I want from a drop down list or combobox and then hide the remaining columns based on the selection. For example if JAN is selected the months FEB through to DEC would be hidden.


View Replies!   View Related
Hide Columns On Column Labels
I was wondering how I might be able to hide columns based on their 1st row labels with vba - I never know where these columns might be on different file I get but I want to hide the same columns every time. right now I am just specifying column numbers or letters but it's not working out:

Columns("A:C").EntireColumn.Hidden = True
Columns("G:I").EntireColumn.Hidden = True
Columns("K:R").EntireColumn.Hidden = True
Columns("T:V").EntireColumn.Hidden = True
ActiveSheet.PrintOut

Columns("A:C").EntireColumn.Hidden = False
Columns("G:I").EntireColumn.Hidden = False
Columns("K:R").EntireColumn.Hidden = False
Columns("T:V").EntireColumn.Hidden = False

View Replies!   View Related
Combining IF With LEFT To Hide Columns VBA
I'm trying to write some code to Hide columns if the first 3 characters of cells in a range equal the contents of another (formula equivalent would be something like this =IF((LEFT(A2,3))=A1,"HIDE COLUMN", "SHOW COLUMN"). I have gotten this far but cant get it to work;-

Private Sub Worksheet_Change(ByVal Target As Range)

Dim r As Range, cell As Range

On Error GoTo ErrHandler
Set r = Me.Range("B7:CG7")

Application.ScreenUpdating = False
Application.EnableEvents = False

Row = 1
col = 1

For Each cell In r
If cell.Value = "" And Left(cell.Value, 3) = cell(Row, col).Value Then
cell.EntireColumn.Hidden = True
Else
cell.EntireColumn.Hidden = False

End If

Next
ErrHandler:
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

View Replies!   View Related
Hide & Unhide Columns With Password
I have a range of columns ( J:AH) which i have to hide from the user interface.In order to unhide, the editor must enter a password. He/ She can only enter the password thrice. After 3 times, if it is still wrong, the whole workbook must be closed. Is this possible? If not, I'll settle for something less complicated.

View Replies!   View Related
Hide Or Unhide Columns With One Macro
I have a single button I want to use to call a macro to:
1.Hide columns C:AZ if they arent already hidden
2.Unhide columns C:AZ if they are already hidden.

View Replies!   View Related
Copyright 2005-08 www.BigResource.com, All rights reserved