Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hiding a column of hyperlinks behind a column of numbers | New Users to Excel | |||
Hiding column if cell in previous column is empty-revised | Excel Programming | |||
Hiding Column Also hiding text | Excel Programming | |||
Need help hiding/unhiding column based on autofilter selection in a different column | Excel Programming | |||
Deleting cells in a column ref a different column | Excel Programming |