I have made a macro that moves information from one sheet to another, but when there are hidden columns it distorts the results. I would like to add code to my macro that will delete the hidden columns.
Printable View
I have made a macro that moves information from one sheet to another, but when there are hidden columns it distorts the results. I would like to add code to my macro that will delete the hidden columns.
Post your current code.
The deleting of hidden columns would be one of the first procedures in this particular macro. I have a feeling that the appropriate code would be to select the appropriate range, loop through the selected columns, use a true/false statement to determine whether the column is hidden or not, if it isn't hidden than I would skip it, but if it is I would delete it. So far the only education that I have received on loops and if/then statements has been from this forum, I am still very new at this and don't know how to correctly use these tools.
I found the below code at: 'http://www.sowsoft.com/excel-macros.htm
I think that it is sort of what I am looking for except it deletes rows instead of columns, as well as, searches all worksheets. My macro will be used with the appropriate worksheet showing so I will not have to loop through all of the worksheets. I have played with this for a while without luck.
VB Code:
For i = 1 To Worksheets.Count If Worksheets(i).Visible Then Worksheets(i).Select ActiveCell.SpecialCells(xlLastCell).Select k = ActiveCell.Row For j = 1 To k If Rows(j).Hidden Then Rows(j).Hidden = False Rows(j).Delete End If Next j End If Next i If Worksheets(1).Visible Then Worksheets(1).Select
Try this version instead
VB Code:
Dim lstcol As Integer lstcol = ActiveCell.SpecialCells(xlCellTypeLastCell).Column Dim i As Integer For i = lstcol To 1 Step -1 If Cells(1, i).EntireColumn.Hidden Then Cells(1, i).EntireColumn.Delete End If Next i