Get Hidden Rows and Columns

[vijay2482]

Hi all,

I'm working on Windows fom application in vb.net. I want to get the hidden rows and columns in an excel file and make them visable and also delete few of them.

I have written the below code, but it doesn't do what i wanted to do. Not sure where i'm wrong.

 Dim lastRow As Integer
                    Dim lastColumn As Integer

                    For lastRow = 1 To rng.Rows.Count
                        For lastColumn = 1 To rng.Columns.Count
 lastColumn = +lastColumn
                        Next
                        lastRow = +lastRow
                    Next


 For lp = lastColumn To 1 Step -1 'loop through all columns
                        If oWorksheet.Columns(lp).EntireColumn.Hidden = True Then
                            oWorksheet.Columns(lp).EntireColumn.Delete()
                        End If
                    Next
                    For lp = lastRow To 1 Step -1 'loop through all rows
                        If oWorksheet.Rows(lp).EntireRow.Hidden = True Then
                            oWorksheet.Rows(lp).EntireRow.Delete()
                        End If
                    Next

Thanks in advance.

[techgnome]

IF you want to unhide it, why are you deleting them?

if .Hidden = True means it's been hidden... what would be the logical thing to do to unHide it?

-tg

[digitalShaman]

speaking of Logical things: this is nonsense:

                    For lastRow = 1 To rng.Rows.Count
                        For lastColumn = 1 To rng.Columns.Count
 lastColumn = +lastColumn
                        Next
                        lastRow = +lastRow
                    Next

why the Loop when you already got rng.Rows.Count/rng.Columns.Count?

and another waste of bits: you don't Need an entirecolumn/entirerow when you Access .columns(x)/.rows(x) as it already is the entire column/row

[vijay2482]

I want to delete it because, I'm converting the excel later to pdf.

Te below is the code i'm using and it doesn't work.

                         lastColumn = rng.Columns.Count
                         lastRow = rng.Rows.Count

                         For lp = lastColumn To 0 Step -1
                         If oWorksheet.Columns(lp).Hidden = True Then
                         oWorksheet.Columns(lp).Delete()

                         End If
                         Next

                         For lp = lastRow To 0 Step -1
                         If oWorksheet.Rows(lp).Hidden = True Then
                             oWorksheet.Rows(lp).Delete()
                          End If

[digitalShaman]

you Need to be more specific. "it doesn't work" or "it doesn't do what i wanted to do." is not giving any clue on what it does nor what you want it to do.

some things i could think of are:
- you are missing the "next" Statement in the second Loop
- your last Loop Iteration is working on col/row 0 which i do not think is valid for Excel, it should be 1 instead
- you determin the number of rows and cols based on a range object (rng) but then work on the worksheet, column 1 in the range is not column 1 in the sheet