Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old December 21st 08, 07:08 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Dec 2008
Posts: 1
Default Hiding a column vs. Deleting a Column

To Whomever can point me in the right direction ...

I need guidance concerning "Hiding" a column of data. Currently my
code below can successfully "Delete" empty columns of data ... but I'm
striking out trying to convert the logic to only 'Hide' the empty
column(s) ...

I have a report that's generated by a web based program. I select the
entire report using CNTL A ... then copy to the clipboard with CNTL
C ... (The size of the report can change in Rows and content within
the Columns, but the table of data always contains a Column Header
that contains a unique name for each Column from Column A thru Column
GD.

In Excel, I have a macro that copies whatever's on the clipboard into
the blank excel worksheet. (That's my process to get DATA into the
spreadsheet.) and then the same macro begins to call some routines to
'process' the data.

One of the steps is to eliminate all the columns in the table with no
data. My code below successfully loops thru the range of data,
determines which columns do NOT have data (excluding the Header
line) ... and then deletes the full column.

No problem so far ... but it's not what I've wanted to do ... I really
need to 'hide' the column ... not delete ...

Can someone please look thru this code which is called after the
Clipboard data is available in the Spreadsheet ... and show me how to
tweak the code it so I can accomplish the task without destroying the
columns altogether?

Note: That the routine - Function GetCoLLet(ColNumber As Long) As
String
I got from another User's post, knowing I have to convert Column
Number to Column letters ... and that's where the hangup is coming ...
I think?

================================
Sub Remove_Columns_with_No_Data()
' Remove COLUMNS containing ONLY a HEADER, but no DATA

Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select

Dim i As Long ' Loop counter
Dim si As String
Dim irow As Long: Dim icol As Long
icol = Range(Selection, ActiveCell.SpecialCells
(xlLastCell)).Columns.Count ' Last Column
irow = Range(Selection, ActiveCell.SpecialCells
(xlLastCell)).Rows.Count ' Last Row
For i = icol To 1 Step -1
If Application.CountA(Cells(2, i).Resize(irow, 1)) = 0 Then
si = GetCoLLet(i) 'go convert the Column number to a letter
(for the HIDE)
si = si & ":" & si

' THIS WORKS FINE?
Columns(CInt(i)).Delete

' Trying to Substitue the Following line for the previous - BUT IT
WON'T WORK?
' Columns(Chr$(34) & si & Chr$(34)).EntireColumn.Hidden = True

End If

Next
End Sub

Function GetCoLLet(ColNumber As Long) As String
' =getcollet(256) returns IV
' Gord Dibben MS Excel MVP
GetCoLLet = Left(Cells(1, ColNumber).Address(False, False), _
1 - (ColNumber 26))
End Function
========================================

Thanks to anyone who takes the time ...

Jim Pellechi

  #2   Report Post  
Old December 21st 08, 07:46 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 3,986
Default Hiding a column vs. Deleting a Column

Try changing this:

Columns(CInt(i)).Delete

To This:

Columns(CInt(i)).Hidden = True



" wrote:

To Whomever can point me in the right direction ...

I need guidance concerning "Hiding" a column of data. Currently my
code below can successfully "Delete" empty columns of data ... but I'm
striking out trying to convert the logic to only 'Hide' the empty
column(s) ...

I have a report that's generated by a web based program. I select the
entire report using CNTL A ... then copy to the clipboard with CNTL
C ... (The size of the report can change in Rows and content within
the Columns, but the table of data always contains a Column Header
that contains a unique name for each Column from Column A thru Column
GD.

In Excel, I have a macro that copies whatever's on the clipboard into
the blank excel worksheet. (That's my process to get DATA into the
spreadsheet.) and then the same macro begins to call some routines to
'process' the data.

One of the steps is to eliminate all the columns in the table with no
data. My code below successfully loops thru the range of data,
determines which columns do NOT have data (excluding the Header
line) ... and then deletes the full column.

No problem so far ... but it's not what I've wanted to do ... I really
need to 'hide' the column ... not delete ...

Can someone please look thru this code which is called after the
Clipboard data is available in the Spreadsheet ... and show me how to
tweak the code it so I can accomplish the task without destroying the
columns altogether?

Note: That the routine - Function GetCoLLet(ColNumber As Long) As
String
I got from another User's post, knowing I have to convert Column
Number to Column letters ... and that's where the hangup is coming ...
I think?

================================
Sub Remove_Columns_with_No_Data()
' Remove COLUMNS containing ONLY a HEADER, but no DATA

Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select

Dim i As Long ' Loop counter
Dim si As String
Dim irow As Long: Dim icol As Long
icol = Range(Selection, ActiveCell.SpecialCells
(xlLastCell)).Columns.Count ' Last Column
irow = Range(Selection, ActiveCell.SpecialCells
(xlLastCell)).Rows.Count ' Last Row
For i = icol To 1 Step -1
If Application.CountA(Cells(2, i).Resize(irow, 1)) = 0 Then
si = GetCoLLet(i) 'go convert the Column number to a letter
(for the HIDE)
si = si & ":" & si

' THIS WORKS FINE?
Columns(CInt(i)).Delete

' Trying to Substitue the Following line for the previous - BUT IT
WON'T WORK?
' Columns(Chr$(34) & si & Chr$(34)).EntireColumn.Hidden = True

End If

Next
End Sub

Function GetCoLLet(ColNumber As Long) As String
' =getcollet(256) returns IV
' Gord Dibben MS Excel MVP
GetCoLLet = Left(Cells(1, ColNumber).Address(False, False), _
1 - (ColNumber 26))
End Function
========================================

Thanks to anyone who takes the time ...

Jim Pellechi



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

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
Hiding a column of hyperlinks behind a column of numbers cmiling New Users to Excel 2 November 20th 09 06:02 PM
Hiding column if cell in previous column is empty-revised [email protected] Excel Programming 2 January 4th 07 07:45 AM
Hiding Column Also hiding text Cindy Excel Programming 0 April 6th 06 07:18 PM
Need help hiding/unhiding column based on autofilter selection in a different column kcleere Excel Programming 1 January 23rd 06 07:21 AM
Deleting cells in a column ref a different column Daminc[_23_] Excel Programming 8 December 23rd 05 06:04 PM


All times are GMT +1. The time now is 02:37 AM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017