Skip Hidden Columns

I need some code to select a range starting at a1 and going across 3 rows and down to the bottom of the sheet(the last cell with any data e.g. lastrow)

currently the columns are a, b, d as column c is hidden.

Is offset the right coding to go with?

column D can't be hard coded in as the number of columns hidden will change. today only C is hidden tomorrow it could be more.

Any help is appreciated

Try this:

Sub Select_Range()
Dim ws As Worksheet:    Set ws = Sheets("Sheet1")

ws.Range(ws.Cells(1, 1), ws.Cells(ActiveSheet.UsedRange.Rows.Count, ActiveSheet.UsedRange.Columns.Count)).SpecialCells(xlCellTypeVisible).Select

End Sub

Originally Posted by stnkynts

Try this:

Sub Select_Range()
Dim ws As Worksheet:    Set ws = Sheets("Sheet1")

ws.Range(ws.Cells(1, 1), ws.Cells(ActiveSheet.UsedRange.Rows.Count, ActiveSheet.UsedRange.Columns.Count)).SpecialCells(xlCellTypeVisible).Select

End Sub

Is there a way to get this code to only select across a certain number of columns as this code seems to be selecting everything on the sheet. there is data in columns E onwards but I only want to select the first 3 visible columns

Is there a way to get this code to only select across a certain number of columns as this code seems to be selecting everything on the sheet.

Of course. I misread your original post. Sorry about that:

Sub Select_Range()
Dim ws As Worksheet:    Set ws = Sheets("Sheet1")
Dim iCol As Integer, myCol As Integer, numVisible As Integer

myCol = 0
numVisible = 0

For iCol = 1 To ws.UsedRange.Columns.Count
    If ws.Cells(1, iCol).EntireColumn.Hidden = False Then
        numVisible = numVisible + 1
    End If

    If numVisible = 3 Then
        myCol = iCol
        Exit For
    End If
Next iCol

ws.Range(ws.Cells(1, 1), ws.Cells(ws.UsedRange.Rows.Count, myCol)).SpecialCells(xlCellTypeVisible).Select

End Sub

Originally Posted by stnkynts

Of course. I misread your original post. Sorry about that:

Sub Select_Range()
Dim ws As Worksheet:    Set ws = Sheets("Sheet1")
Dim iCol As Integer, myCol As Integer, numVisible As Integer

myCol = 0
numVisible = 0

For iCol = 1 To ws.UsedRange.Columns.Count
    If ws.Cells(1, iCol).EntireColumn.Hidden = False Then
        numVisible = numVisible + 1
    End If

    If numVisible = 3 Then
        myCol = iCol
        Exit For
    End If
Next iCol

ws.Range(ws.Cells(1, 1), ws.Cells(ws.UsedRange.Rows.Count, myCol)).SpecialCells(xlCellTypeVisible).Select

End Sub

This code is perfect. Is there also a variation i can make to the code if i wanted to start the selection at a particular row e.g. row A10 or starting it at a different column in the sheet?

ws.Range(ws.Cells(1, 1), ws.Cells(ws.UsedRange.Rows.Count, myCol)).SpecialCells(xlCellTypeVisible).Select

The above line is the range. You can alter your range by changing the parameters here. For example: ws.Cells(1,1) is A1. ws.Cells(10, 1) would be A10. Starting at a different column would also require you to change the iCol start. In other words if you wanted to start at column C it would be For iCol = 3 to ws.UsedRange.Columns.Count and ws.Cells(1, 3) respectively.

Originally Posted by stnkynts

ws.Range(ws.Cells(1, 1), ws.Cells(ws.UsedRange.Rows.Count, myCol)).SpecialCells(xlCellTypeVisible).Select

The above line is the range. You can alter your range by changing the parameters here. For example: ws.Cells(1,1) is A1. ws.Cells(10, 1) would be A10. Starting at a different column would also require you to change the iCol start. In other words if you wanted to start at column C it would be For iCol = 3 to ws.UsedRange.Columns.Count and ws.Cells(1, 3) respectively.

Thank you very much for this!!!!!!!!!!!!!!!