I am trying to hide four columns on my worksheet, but for some strange reason I am getting the following message:
"Run Time Error 1004
Unable to Set The Hidden Property of the Range Class"
I have no merged cells or objects in those columns. Has anyone else suffered this problem?
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'm trying to hide all columns which have the word "hide" in row 6. I have done a similar thing whereby I hide all rows which have the word "hide" in column 3 using the following
Sub HURows()
BeginRow = 9
EndRow = 40
ChkCol = 3
For RowCnt = BeginRow To EndRow
If Cells(RowCnt, ChkCol).Value = "hide" Then
Cells(RowCnt, ChkCol).EntireRow.Hidden = True
Else
Cells(RowCnt, ChkCol).EntireRow.Hidden = False
End If
Next RowCnt
End Sub
Alas changing the number and every Col for Row and vice versa doesn't work!! Really I only need to search colums G to U inclusive. The code must also unhide colums if the values in the cells of row 6 change to anything other than "hide".
I'm trying to create a code that when run hides a selection of columns and defines the minimum and interval value for a chart on the active worksheet. My attempt is assigned to the 'update skills' button at the top of the "GRAPH" worksheet of the attached workbook.
View 2 Replies View RelatedI have some vba code that is used to hide any rows with the cell value of "Finished". The code is below. When I run this code I get a run time error 424 - Object Required. Can you please explain or help as to why I am getting this?
As a side point the data is loaded from an oracle database rather than from the sheet but every other function/code works fine.
I am setting up a summary sheet that contains =indirect() functions for workbooks that don't exist yet.
I would like some kind of function that returns the =indirect() function correctly if the workbook exists and just a 0 or blank if the workbook doesn't exist.
I would like to have the indirect function in all of cells that as soon as someone creates a workbook it will update the summary sheet.
I pieced together this code last night but I cannot seem to get the hide row function to work correctly. Any ideas on how I can correct the hiding rows portion? I just want the code to hide the entire row if columns K:Z are all blank. So I assumed I can make the CountBlanks=16, then hide that row if its true! Thanks to Sixth Sense and Norrie for that idea!
Code: ....
I'm relatively new to this, and its entirely possibly (more likely probable) that I am attempting to this in the wrong way, but here's what I'm dealing with...
View 8 Replies View RelatedI'm trying to run this macro and I keep getting the "Run-time error '1004' Application-defined or object-defined error"
I am just trying to hide the rows that do not contain anything in them with this
x = 2
Sheets("sheet2").select
If Cells(x,1).value = "" Then
Rows("x:x").select
Selection.EntireRow.Hidden = True
Else
x = x +1
End if
The line that is giving me a problem is "Rows("x:x").select". but when i change x to an actual number, it works. I cannot have a definitive number because where there is data will vary every time this is ran.
I've got the following formula in a column: =IF(C10/B10>=1,20,IF(ISERR(C10/B10),0,0)). The purpose is to ensure that only one of two values (20 or 0) is displayed, regardless of circumstances that regularly generate the # DIV/0 error. The if(iserr(...)) part works fine when tested on its own, but when nested, it's still allowing the error to display.
View 3 Replies View RelatedI want to hide the row of player on the ATP Stats worksheet that have 0's in all competition, the file example can be download in next link.
View 14 Replies View RelatedI have a spreadsheet which has 3 separate external queries running from worksheet 'A'. My user enters a couple of dates in another worksheet 'B' to supply the date parameters for the query and a macro runs to refresh all of the queries and performs a few calculations.This works fine. The trouble is I don't want the user to be able to view all the data on sheet 'A' only the summary on B. My macro ends up on sheet B but whilst the query is refreshing the raw data is displayed to the user on sheet 'A'. When I hide the sheet 'A' i get a run time error '1004' Select method of worksheet class failed.
View 4 Replies View Relatedautomatically 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.
View 14 Replies View RelatedI have some code which looks for the word "Out" in column "L" and hides the row if it finds it. This is running from row 24 to 160 and takes about a minute to run. I've tried two different solutions, neither really quicker than the other.
View 3 Replies View RelatedI found this code for hiding an autofilter arrow for column 2. I would also like to hide the arrows for columns 35 through 50. Does anybody know how to do that? Thanks.
Private Sub Workbook_Open()
'hides all arrows except column 2
Dim c As Range
Dim i As Integer
i = Cells(1, 1).End(xlToRight).Column
Application.ScreenUpdating = False
For Each c In Range(Cells(1, 1), Cells(1, i))
If c.Column 25 Then
c.AutoFilter Field:=c.Column, _
Visibledropdown:=False
End If
Next
Application.ScreenUpdating = True
End Sub
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 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?
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).
I have a spreadsheet that i would like to hide all columns from B:M and to select the column/s I want to unhide.
I would like VBA cose to do this or to set up a combo box that will allow me to hide/unhide specific columns....
I need help on hiding a row if a cell value is 0 in a column
View 5 Replies View RelatedI have a some simple code that doesn't seem to want to work ALL the time. Granted, sometimes it works but not always. The first code is to hide a bunch of columns and the second is to display those again. I put in the If/Then to avoid trying to hide columns already hidden (I thgouht that had to be done - true). Anyone see any problems with this code. The error I get is:
"Run-time error '1004':
Unable to set the hidden propoerty of Range class. I get it at
If wb.Worksheets("Growth").Columns("ap:iv").EntireColumn.Hidden = True Then
wb.Worksheets("Growth").Columns("ap:iv").EntireColumn.Hidden = False 'Error is on this line
End If..............
I'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 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.
I was messing around with the following code:
[Code] .....
But it doesn't seem to work.
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 trying to hide columns where the Value in the cells on row 9 is "", that cell being populated with a formula where the result is "". However I am getting the Compile Error Message 'Next without For'. Any clues?
Sub GraphC()
Dim a As Integer
Dim ColumnVar As Variant
ColumnVar = Array("B", "C", "D", "E", "F", "G", "H", "I", "J", "K")
[Code] .........
using VBA in excel. My questions is: I would like to make a listbox that has 1 column and shows all of the headers in my different worksheet columns, and I would like to hide the columns based on which ones are selected in the listbox, is this possible I am having trouble findings examples online.
View 9 Replies View RelatedTrying to hide columns that have a date in any cell: cell < TODAY() Or cell - 30 < TODAY()
Code:
Dim row As Range
Dim cell As Range
Dim col As Range
Dim lRw As Long
[Code] .......
I'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.