Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Hide Columns
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 45
Default Hide Columns
Hi,

I have a hide rows macro that I got off here a few weeks back but i would
now like to change it so it can hide columns, I'm very programming illiterate
and would like soem help please, here is the hide rows macro

Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

Firstrow = ActiveSheet.Cells(1).Row
Lastrow = ActiveSheet.Rows.Count + Firstrow - 1
With ActiveSheet
.DisplayPageBreaks = False
For Lrow = Lastrow To Firstrow Step -1

If .Cells(Lrow, "A").Value = "0" And _
.Cells(Lrow, "A").Value = "0" Then .Rows(Lrow).Hidden = False

Next
End With
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With

if someone can change it i would be very greatfull, i have tried but can't
get it to work, many thanks,

Andy
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default Hide Columns
Dim Firstcol As Long
Dim Lastcol As Long
Dim Lcol As Long
Dim CalcMode As Long
Dim lRow as Long

' check in row 1
lRow = 1

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

Firstcol = 1
Lastcol = cells(lRow,"IV").End(xltoLeft).Column
With ActiveSheet
.DisplayPageBreaks = False
For LCol = LastCol To FirstCol Step -1

If .Cells(lRow,LCol).Value = "0" And _
.Cells(lRow,LCol").Value = "0" Then
.Columns(LCol).Hidden = False
End If

Next
End With
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With

End sub

this checks entries in Row 1, so if you want the hiding based on another
row, change the value of lRow from 1 to something else.

I left in the double check in the If statement in case you want to check for
two conditions.

--
Regards,
Tom Ogilvy

"Momo" wrote in message
...
Hi,

I have a hide rows macro that I got off here a few weeks back but i would
now like to change it so it can hide columns, I'm very programming
illiterate
and would like soem help please, here is the hide rows macro

Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

Firstrow = ActiveSheet.Cells(1).Row
Lastrow = ActiveSheet.Rows.Count + Firstrow - 1
With ActiveSheet
.DisplayPageBreaks = False
For Lrow = Lastrow To Firstrow Step -1

If .Cells(Lrow, "A").Value = "0" And _
.Cells(Lrow, "A").Value = "0" Then .Rows(Lrow).Hidden =
False

Next
End With
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With

if someone can change it i would be very greatfull, i have tried but can't
get it to work, many thanks,

Andy


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 45
Default Hide Columns
hi thanks,

I'm not sure if it works but thus far I get the following in red with a
compile error message?

If .Cells(lRow,LCol).Value = "0" And _
.Cells(lRow,LCol").Value = "0" Then

"Tom Ogilvy" wrote:

Dim Firstcol As Long
Dim Lastcol As Long
Dim Lcol As Long
Dim CalcMode As Long
Dim lRow as Long

' check in row 1
lRow = 1

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

Firstcol = 1
Lastcol = cells(lRow,"IV").End(xltoLeft).Column
With ActiveSheet
.DisplayPageBreaks = False
For LCol = LastCol To FirstCol Step -1

If .Cells(lRow,LCol).Value = "0" And _
.Cells(lRow,LCol").Value = "0" Then
.Columns(LCol).Hidden = False
End If

Next
End With
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With

End sub

this checks entries in Row 1, so if you want the hiding based on another
row, change the value of lRow from 1 to something else.

I left in the double check in the If statement in case you want to check for
two conditions.

--
Regards,
Tom Ogilvy

"Momo" wrote in message
...
Hi,

I have a hide rows macro that I got off here a few weeks back but i would
now like to change it so it can hide columns, I'm very programming
illiterate
and would like soem help please, here is the hide rows macro

Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

Firstrow = ActiveSheet.Cells(1).Row
Lastrow = ActiveSheet.Rows.Count + Firstrow - 1
With ActiveSheet
.DisplayPageBreaks = False
For Lrow = Lastrow To Firstrow Step -1

If .Cells(Lrow, "A").Value = "0" And _
.Cells(Lrow, "A").Value = "0" Then .Rows(Lrow).Hidden =
False

Next
End With
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With

if someone can change it i would be very greatfull, i have tried but can't
get it to work, many thanks,

Andy



Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default Hide Columns
If .Cells(lRow,LCol).Value = "0" And _
.Cells(lRow,LCol").Value = "0" Then

should be

If .Cells(lRow,LCol).Value = "0" And _
.Cells(lRow,LCol).Value = "0" Then

Remove the extra double quote which didn't get deleted when I edited the
code.


I have tested this version and it worked for me: (I also changed .Hidden =
False to .Hidden = True)

Sub Macro1()
Dim Firstcol As Long
Dim Lastcol As Long
Dim Lcol As Long
Dim CalcMode As Long
Dim lRow As Long

' check in row 1
lRow = 1

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

Firstcol = 1
Lastcol = Cells(lRow, "IV").End(xlToLeft).Column
With ActiveSheet
.DisplayPageBreaks = False
For Lcol = Lastcol To Firstcol Step -1

If .Cells(lRow, Lcol).Value = "0" And _
.Cells(lRow, Lcol).Value = "0" Then
Debug.Print Lcol
.Columns(Lcol).Hidden = True
End If

Next
End With
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With

End Sub

--
Regards,
Tom Ogilvy


"Momo" wrote in message
...
hi thanks,

I'm not sure if it works but thus far I get the following in red with a
compile error message?

If .Cells(lRow,LCol).Value = "0" And _
.Cells(lRow,LCol").Value = "0" Then

"Tom Ogilvy" wrote:

Dim Firstcol As Long
Dim Lastcol As Long
Dim Lcol As Long
Dim CalcMode As Long
Dim lRow as Long

' check in row 1
lRow = 1

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

Firstcol = 1
Lastcol = cells(lRow,"IV").End(xltoLeft).Column
With ActiveSheet
.DisplayPageBreaks = False
For LCol = LastCol To FirstCol Step -1

If .Cells(lRow,LCol).Value = "0" And _
.Cells(lRow,LCol").Value = "0" Then
.Columns(LCol).Hidden = False
End If

Next
End With
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With

End sub

this checks entries in Row 1, so if you want the hiding based on another
row, change the value of lRow from 1 to something else.

I left in the double check in the If statement in case you want to check
for
two conditions.

--
Regards,
Tom Ogilvy

"Momo" wrote in message
...
Hi,

I have a hide rows macro that I got off here a few weeks back but i
would
now like to change it so it can hide columns, I'm very programming
illiterate
and would like soem help please, here is the hide rows macro

Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

Firstrow = ActiveSheet.Cells(1).Row
Lastrow = ActiveSheet.Rows.Count + Firstrow - 1
With ActiveSheet
.DisplayPageBreaks = False
For Lrow = Lastrow To Firstrow Step -1

If .Cells(Lrow, "A").Value = "0" And _
.Cells(Lrow, "A").Value = "0" Then .Rows(Lrow).Hidden =
False

Next
End With
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With

if someone can change it i would be very greatfull, i have tried but
can't
get it to work, many thanks,

Andy





Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,824
Default Hide Columns
Did you mean this?

If .Cells(lRow, Lcol).Value = "0" And _
.Cells(lRow, Lcol).Value = "0" Then

or this?

If .Cells(lRow, Lcol).Value = "0" And _
.Cells(lRow, Lcol).Value = 0 Then

They looked strikingly similar <bg.



Tom Ogilvy wrote:

If .Cells(lRow,LCol).Value = "0" And _
.Cells(lRow,LCol").Value = "0" Then

should be

If .Cells(lRow,LCol).Value = "0" And _
.Cells(lRow,LCol).Value = "0" Then

Remove the extra double quote which didn't get deleted when I edited the
code.

I have tested this version and it worked for me: (I also changed .Hidden =
False to .Hidden = True)

Sub Macro1()
Dim Firstcol As Long
Dim Lastcol As Long
Dim Lcol As Long
Dim CalcMode As Long
Dim lRow As Long

' check in row 1
lRow = 1

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

Firstcol = 1
Lastcol = Cells(lRow, "IV").End(xlToLeft).Column
With ActiveSheet
.DisplayPageBreaks = False
For Lcol = Lastcol To Firstcol Step -1

If .Cells(lRow, Lcol).Value = "0" And _
.Cells(lRow, Lcol).Value = "0" Then
Debug.Print Lcol
.Columns(Lcol).Hidden = True
End If

Next
End With
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With

End Sub

--
Regards,
Tom Ogilvy

"Momo" wrote in message
...
hi thanks,

I'm not sure if it works but thus far I get the following in red with a
compile error message?

If .Cells(lRow,LCol).Value = "0" And _
.Cells(lRow,LCol").Value = "0" Then

"Tom Ogilvy" wrote:

Dim Firstcol As Long
Dim Lastcol As Long
Dim Lcol As Long
Dim CalcMode As Long
Dim lRow as Long

' check in row 1
lRow = 1

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

Firstcol = 1
Lastcol = cells(lRow,"IV").End(xltoLeft).Column
With ActiveSheet
.DisplayPageBreaks = False
For LCol = LastCol To FirstCol Step -1

If .Cells(lRow,LCol).Value = "0" And _
.Cells(lRow,LCol").Value = "0" Then
.Columns(LCol).Hidden = False
End If

Next
End With
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With

End sub

this checks entries in Row 1, so if you want the hiding based on another
row, change the value of lRow from 1 to something else.

I left in the double check in the If statement in case you want to check
for
two conditions.

--
Regards,
Tom Ogilvy

"Momo" wrote in message
...
Hi,

I have a hide rows macro that I got off here a few weeks back but i
would
now like to change it so it can hide columns, I'm very programming
illiterate
and would like soem help please, here is the hide rows macro

Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

Firstrow = ActiveSheet.Cells(1).Row
Lastrow = ActiveSheet.Rows.Count + Firstrow - 1
With ActiveSheet
.DisplayPageBreaks = False
For Lrow = Lastrow To Firstrow Step -1

If .Cells(Lrow, "A").Value = "0" And _
.Cells(Lrow, "A").Value = "0" Then .Rows(Lrow).Hidden =
False

Next
End With
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With

if someone can change it i would be very greatfull, i have tried but
can't
get it to work, many thanks,

Andy




--

Dave Peterson

Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default Hide Columns
I meant the first which should look strikingly identical. That is the OP's
code as far as what is being tested - and I so stated in my first post that
I left that in in case he/she had some reason to have duplicate tests.
(placeholder or whatever).

Unless you are seeing something and I am missing it. (but testing "0" or 0
with an and ?? although a true zero would pass both while a blank or "0"
would not - at least in xl97)

--
Regards,
Tom Ogilvy


"Dave Peterson" wrote in message
...
Did you mean this?

If .Cells(lRow, Lcol).Value = "0" And _
.Cells(lRow, Lcol).Value = "0" Then

or this?

If .Cells(lRow, Lcol).Value = "0" And _
.Cells(lRow, Lcol).Value = 0 Then

They looked strikingly similar <bg.



Tom Ogilvy wrote:

If .Cells(lRow,LCol).Value = "0" And _
.Cells(lRow,LCol").Value = "0" Then

should be

If .Cells(lRow,LCol).Value = "0" And _
.Cells(lRow,LCol).Value = "0" Then

Remove the extra double quote which didn't get deleted when I edited the
code.

I have tested this version and it worked for me: (I also changed
..Hidden =
False to .Hidden = True)

Sub Macro1()
Dim Firstcol As Long
Dim Lastcol As Long
Dim Lcol As Long
Dim CalcMode As Long
Dim lRow As Long

' check in row 1
lRow = 1

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

Firstcol = 1
Lastcol = Cells(lRow, "IV").End(xlToLeft).Column
With ActiveSheet
.DisplayPageBreaks = False
For Lcol = Lastcol To Firstcol Step -1

If .Cells(lRow, Lcol).Value = "0" And _
.Cells(lRow, Lcol).Value = "0" Then
Debug.Print Lcol
.Columns(Lcol).Hidden = True
End If

Next
End With
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With

End Sub

--
Regards,
Tom Ogilvy

"Momo" wrote in message
...
hi thanks,

I'm not sure if it works but thus far I get the following in red with
a
compile error message?

If .Cells(lRow,LCol).Value = "0" And _
.Cells(lRow,LCol").Value = "0" Then

"Tom Ogilvy" wrote:

Dim Firstcol As Long
Dim Lastcol As Long
Dim Lcol As Long
Dim CalcMode As Long
Dim lRow as Long

' check in row 1
lRow = 1

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

Firstcol = 1
Lastcol = cells(lRow,"IV").End(xltoLeft).Column
With ActiveSheet
.DisplayPageBreaks = False
For LCol = LastCol To FirstCol Step -1

If .Cells(lRow,LCol).Value = "0" And _
.Cells(lRow,LCol").Value = "0" Then
.Columns(LCol).Hidden = False
End If

Next
End With
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With

End sub

this checks entries in Row 1, so if you want the hiding based on
another
row, change the value of lRow from 1 to something else.

I left in the double check in the If statement in case you want to
check
for
two conditions.

--
Regards,
Tom Ogilvy

"Momo" wrote in message
...
Hi,

I have a hide rows macro that I got off here a few weeks back but
i
would
now like to change it so it can hide columns, I'm very programming
illiterate
and would like soem help please, here is the hide rows macro

Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

Firstrow = ActiveSheet.Cells(1).Row
Lastrow = ActiveSheet.Rows.Count + Firstrow - 1
With ActiveSheet
.DisplayPageBreaks = False
For Lrow = Lastrow To Firstrow Step -1

If .Cells(Lrow, "A").Value = "0" And _
.Cells(Lrow, "A").Value = "0" Then
..Rows(Lrow).Hidden =
False

Next
End With
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With

if someone can change it i would be very greatfull, i have tried
but
can't
get it to work, many thanks,

Andy




--

Dave Peterson



Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,824
Default Hide Columns
Oops. I didn't read the entire thread--or even the portion quoted in your test.


Tom Ogilvy wrote:

I meant the first which should look strikingly identical. That is the OP's
code as far as what is being tested - and I so stated in my first post that
I left that in in case he/she had some reason to have duplicate tests.
(placeholder or whatever).

Unless you are seeing something and I am missing it. (but testing "0" or 0
with an and ?? although a true zero would pass both while a blank or "0"
would not - at least in xl97)

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
Did you mean this?

If .Cells(lRow, Lcol).Value = "0" And _
.Cells(lRow, Lcol).Value = "0" Then

or this?

If .Cells(lRow, Lcol).Value = "0" And _
.Cells(lRow, Lcol).Value = 0 Then

They looked strikingly similar <bg.



Tom Ogilvy wrote:

If .Cells(lRow,LCol).Value = "0" And _
.Cells(lRow,LCol").Value = "0" Then

should be

If .Cells(lRow,LCol).Value = "0" And _
.Cells(lRow,LCol).Value = "0" Then

Remove the extra double quote which didn't get deleted when I edited the
code.

I have tested this version and it worked for me: (I also changed
.Hidden =
False to .Hidden = True)

Sub Macro1()
Dim Firstcol As Long
Dim Lastcol As Long
Dim Lcol As Long
Dim CalcMode As Long
Dim lRow As Long

' check in row 1
lRow = 1

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

Firstcol = 1
Lastcol = Cells(lRow, "IV").End(xlToLeft).Column
With ActiveSheet
.DisplayPageBreaks = False
For Lcol = Lastcol To Firstcol Step -1

If .Cells(lRow, Lcol).Value = "0" And _
.Cells(lRow, Lcol).Value = "0" Then
Debug.Print Lcol
.Columns(Lcol).Hidden = True
End If

Next
End With
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With

End Sub

--
Regards,
Tom Ogilvy

"Momo" wrote in message
...
hi thanks,

I'm not sure if it works but thus far I get the following in red with
a
compile error message?

If .Cells(lRow,LCol).Value = "0" And _
.Cells(lRow,LCol").Value = "0" Then

"Tom Ogilvy" wrote:

Dim Firstcol As Long
Dim Lastcol As Long
Dim Lcol As Long
Dim CalcMode As Long
Dim lRow as Long

' check in row 1
lRow = 1

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

Firstcol = 1
Lastcol = cells(lRow,"IV").End(xltoLeft).Column
With ActiveSheet
.DisplayPageBreaks = False
For LCol = LastCol To FirstCol Step -1

If .Cells(lRow,LCol).Value = "0" And _
.Cells(lRow,LCol").Value = "0" Then
.Columns(LCol).Hidden = False
End If

Next
End With
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With

End sub

this checks entries in Row 1, so if you want the hiding based on
another
row, change the value of lRow from 1 to something else.

I left in the double check in the If statement in case you want to
check
for
two conditions.

--
Regards,
Tom Ogilvy

"Momo" wrote in message
...
Hi,

I have a hide rows macro that I got off here a few weeks back but
i
would
now like to change it so it can hide columns, I'm very programming
illiterate
and would like soem help please, here is the hide rows macro

Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

Firstrow = ActiveSheet.Cells(1).Row
Lastrow = ActiveSheet.Rows.Count + Firstrow - 1
With ActiveSheet
.DisplayPageBreaks = False
For Lrow = Lastrow To Firstrow Step -1

If .Cells(Lrow, "A").Value = "0" And _
.Cells(Lrow, "A").Value = "0" Then
.Rows(Lrow).Hidden =
False

Next
End With
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With

if someone can change it i would be very greatfull, i have tried
but
can't
get it to work, many thanks,

Andy




--

Dave Peterson


--

Dave Peterson

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Want to Hide columns in spreadsheet but NOT hide data in chart. KrispyData Charts and Charting in Excel 1 March 20th 09 04:45 PM
Hide/Unhide columns using button on top over relevant columns [email protected] Excel Discussion (Misc queries) 1 March 7th 07 09:24 PM
Hide Columns hajo Excel Worksheet Functions 4 September 2nd 06 08:09 PM
Hide columns cad46230 Excel Discussion (Misc queries) 3 May 13th 06 03:52 PM
Cannot Hide Columns Joe A Excel Discussion (Misc queries) 2 December 15th 04 10:22 PM


All times are GMT +1. The time now is 11:48 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement