Find last used column,then find every 3rd cell in that column

[jon321]

I need to find the last used column in my spreadsheet which i already have a working code for:

Sub Button2_Click()
Dim LastRow As Long, LastColumn As Integer

LastRow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row
LastColumn = ActiveSheet.UsedRange.Columns(ActiveSheet.UsedRange.Columns.Count).Column

Columns(LastColumn).Select
End Sub

Then I need to find every 3rd cell in that column, i also have a code for this but i dont know how to make these work as one code as at the minute this is in a set column!

Sub Button1_Click()
Dim rRange As Range
Dim rEveryNth As Range
Dim lRow As Long

With Sheet1
Set rRange = .Range("c1", .Cells(.Rows.Count, "C").End(xlUp))
End With

For lRow = 1 To rRange.Rows.Count Step 3
If lRow = 1 Then
Set rEveryNth = rRange(lRow, 1)
Else
Set rEveryNth = Union(rRange(lRow, 1), rEveryNth)
End If
Next lRow

Application.Goto rEveryNth
End Sub

Please help

 

[stanleydgromjr]

jon321,

Try, for just Button1_Click:

Sub Button1_Click()
    Dim rRange As Range
    Dim rEveryNth As Range
    Dim lRow, lCol As Long
    
    With Sheet1
        lCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
        lRow = Cells(Rows.Count, lCol).End(xlUp).Row
        Set rRange = .Range(Cells(1, lCol), Cells(lRow, lCol))
    End With

    For lRow = 1 To rRange.Rows.Count Step 3
        If lRow = 1 Then
            Set rEveryNth = rRange(lRow, 1)
        Else
            Set rEveryNth = Union(rRange(lRow, 1), rEveryNth)
        End If
    Next lRow
    
    Application.Goto rEveryNth
End Sub

Have a great day,
Stan

 

[MickG]

Hi, This might help

Dim lastcolumn, Col As String, rRange As Range
 lastcolumn = ActiveSheet.UsedRange.Address
   Col = Split(Split(lastcolumn, ":")(1), "$")(1)
      Set rRange = Range(Range(Col & "1"), Range(Col & Rows.Count).End(xlUp))
       MsgBox rRange.Address

Regards Mick

 

[jon321]

Stan that version works great, however i do have a few blank cells dotted around within my formatting, the first code i posted seemed to bypass this and go straight to the exact column i needed whereas yours doesnt if theres a blank cell before that column.. any suggestions??

Many thanks

 

[stanleydgromjr]

jon321,

Try, for just Button1_Click:

Option Explicit
Sub Button1_Click()
    Dim rRange As Range
    Dim rEveryNth As Range
    Dim lRow, lCol As Long
    With Sheet1
        lCol = .UsedRange.Columns(ActiveSheet.UsedRange.Columns.Count).Column
        lRow = Cells(Rows.Count, lCol).End(xlUp).Row
        Set rRange = .Range(Cells(1, lCol), Cells(lRow, lCol))
    End With
    For lRow = 1 To rRange.Rows.Count Step 3
        If lRow = 1 Then
            Set rEveryNth = rRange(lRow, 1)
        Else
            Set rEveryNth = Union(rRange(lRow, 1), rEveryNth)
        End If
    Next lRow
    Application.Goto rEveryNth
End Sub

Have a great day,
Stan

 

[jon321]

that works great!!, final thing though i need it to start a set number of rows from the top.. say 5 or something can i change your code to achieve this?

Thanks

 

[jon321]

Found it consider this SOLVED!!