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

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