Establishing a column number from a variable range

[Red over White]

I'm hoping someone will be able to help me with the following code

Sub HideCols()

Sheets("Sheet1").Activate

Dim Int1 As Integer
With Sheets("Sheet1")
.Columns("L:CR").EntireColumn.Hidden = False
For Int1 = 12 To 96
.Columns(Int1).Hidden = Cells(3, Int1).Value <> 0
Next Int1
End With

Range(Cells(2, 47), Cells(2, 96)).SpecialCells(xlCellTypeBlanks)(1).Select

' Hide columns that aren't required

Dim rng As Range
Dim Col1 As Integer
Col1 = ActiveSheet.Columns(ActiveCell.Column).Rows(1)

' set rng to refer to required range

Set rng = Range(3 & ":" & Col1 - 1)

rng.Select
Selection.EntireColumn.Hidden = True

End Sub

I'm assuming the issue is surrounding Col1, in that it will be in the "A1" format, and should be a number. However, I don't know how to define Col1 as the identified column as a number.

Thanks

 

[Joe4]

Try using the "Cell" structure, which can accept numbers in columns, like you did here:

Range(Cells(2, 47), Cells(2, 96)).SpecialCells(xlCellTypeBlanks)(1).Select

By the way, the left side of your range appears to be problematic too:

Set rng = Range(3 & ":" & Col1 - 1)

Not sure what you are trying to do with that red part, but it does not look correct.

Not sure what you are trying to accomplish here either:

Col1 = ActiveSheet.Columns(ActiveCell.Column).Rows(1)

If you explain what range you are trying to build there, we can probably help you with that part.

 

[Red over White]

Thanks - hopefully this will make it clearer

' To find the first blank cell in row 2 between columns 47 and 96 (earlier part of macro not included)

Range(Cells(2, 47), Cells(2, 96)).SpecialCells(xlCellTypeBlanks)(1).Select

' Set range to Hide columns that aren't required

Dim rng As Range
Dim Col1 As Integer

' trying to set a marker to identify the column
' i'm assuming this is where things are going wrong

Col1 = ActiveSheet.Columns(ActiveCell.Column).Rows(1)

' set rng to refer to required range
' column 3 is a constant and is the first column to be hidden
' Col1 - 1 is the last column (from 3) to be hidden

Set rng = Range(3 & ":" & Col1 - 1)

rng.Select
Selection.EntireColumn.Hidden = True

End Sub

 

[Joe4]

Why don't we take a step back here. It is often very hard to figure out how exactly you want something to work from code that has issues with it.
So, can you just explain in plain English exactly how you want this to work?
Are you wanting to look at all the cells in the range AU2:CR2, and hide the ENTIRE column if the value in that cell is blank (i.e. if AU2 is blank, hide the entire column AU)?

 

[Red over White]

Joe4

I am trying to hide a series of columns from 3 to a variant number.

 

[Joe4]

I am trying to hide a series of columns from 3 to a variant number.

Can you please provide more details?

Let's totally forget about your code right now, and just work on the explanation, to someone who has no knowledge of your issue.
How is this "variant number" to be derived?
What is the logic for determing which columns to hide and which not to?
Are there any other important details we should know?

 

[Red over White]

How is this "variant number" to be derived?
The variant is established using Range(Cells(2, 47), Cells(2, 96)).SpecialCells(xlCellTypeBlanks)(1).Select, Row 2 having the first blank cell in that range of columns
So it is the blank cell in row 2 that is the deciding factor. Because I want to enter something in this cell I don't want it to be hidden, so am trying to select the adjacent column to the left.
What is the logic for determing which columns to hide and which not to?
I want the first column with an empty row 2 to be to the left of the screen, so I can enter data in row 2 and other rows for that column. The logic as to which column show be hidden is in the first section (see original posting) and is based on row 3. There is a formula (for example =IF(Z$68>0,Z$68,0) in that row, but nothing in row 2. Doing the search on row 2 allows me to find the first 'empty' cell.
Are there any other important details we should know?
The initial sort begins at column 12, but I want column 3 onwards to be hidden. This is only because columns 3 - 11 have data I'm not concerned about at this stage so don't need to see them.

 

[Joe4]

Hmmm... Without seeing the companion data, I am finding these explanations a bit confusing. Would it be possible to post a small example, showing us what your sheet looks like, and explaining what you want to happen in that instance? Many times, things become much clearer when we can see and work through an actual example.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Red over White]

Hi again

Column variant.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1												I1	I2	I3	I4	I5	I6	I7	I8
2													I Rate 1	I Rate 2	I Rate 3		I Rate 4
3	19											0.00	0.75	2.10	1.36	0.00	1.30		0.00
4
5	Mar-21												0.75	2.10	1.36		1.30
6	Apr-21											0.00	0.75	2.10	1.36	0.00	1.30		0.00
7	May-21												0.75	2.10	1.36	0.00	1.30
8	Jun-21												0.75	2.10	1.36	0.00	1.30
9	Jul-21												0.75	2.10	1.36	0.00	1.30
10	Aug-21												0.75	2.10	1.36	0.00	1.30
11	Sep-21												0.75	2.10	1.36	0.00	1.30
12	Oct-21												0.75	2.10	1.36	0.00	1.30
13	Nov-21												0.75	2.10	1.36	0.00	1.30
14	Dec-21												0.75	2.10	1.36	0.00	1.30
15	Jan-22												0.75	2.10	1.36	0.00	1.30
16	Feb-22												0.75	2.10	1.36	0.00	1.30
17	Mar-22												0.75	2.10	1.36	0.00	1.30
18	Apr-22												0.75	2.10	1.36	0.00	1.30
19	May-22												0.75	2.10	1.36	0.00	1.30
20
Interest

Cell Formulas
Range	Formula
A3	A3	=COUNT(INDIRECT("A"&"10:A5000"))+9
L3	L3	=IF(L6>0,L6,0)
S3,M3:Q3	M3	=IF(M$7>0,M$7,0)
L6,M19:Q19,S6,M6:Q17	L6	=IF(OR($A6=0,L5=0),0,L5)
A19,A6:A17	A6	=EOMONTH(A5,1)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
CS1:XFD1,AE20:XFD1048576,A20:AC1048576,AE5:AT5,A1:K18,AU5:XFD18,S19:XFD19,A19:Q19,S6:AT18,L2:Q18,L1:Z1,S2:XFD4,S5:AC5	Cell Value	=0	text	NO

I did try and load the whole sheet but it was too big. Using the condensed version, I want to establish the first cell in row 2 that is blank AFTER column M, and then hide columns 3 to the column preceding the blank cell column. Is should select column P and having entered data into that column, run the macro again it would then select column R.

 

[Red over White]

It should select column P (and hide up to O) and having entered data into that column, run the macro again it would then select column R (and hide up to Q).