Select consecutive rows by doubleclick based on cell value in specific column

[somers]

Hello,
im trying to put a vba code together that selects the entirerows with a specific value by doubleclick of a cell.
I have the code working with a fixed range but not by doublecilck.
Sombody see what is wrong here?

Code that works:

Sub Counter()

  Dim Valuehiderow As Long
  Dim y, rng, rng2 As Range
  
Set rng = Range(Range("N54"), Range("N54").End(xlDown))
With rng
    For Each y In .Rows
        If Application.CountIf(y, "INPUT") > 0 Then
            Valuehiderow = Valuehiderow + 1
        End If
    Next
End With

Set rng2 = Range(Range("N54"), Range("N54").Offset(Valuehiderow))
rng2.Select

End Sub

Code doesnt work:

Sub Counter2()

  Dim Valuehiderow As Long
  Dim y, rng, rng2 As Range
  
Set rng = Range(Target.Offset(12), Target.End(xlDown))
        With rng
            For Each y In .Rows
                If Application.CountIf(y, "INPUT") > 0 Then
                Valuehiderow = Valuehiderow + 1
                End If
            Next
        End With
        
Set rng2 = Range(Target.Offset(1), Target.Offset(Valuehiderow))
    rng2.EntireRow.Select
    MsgBox (Valuehiderow)

End Sub

 

[Joe4]

Welcome to the Board!

"Target" is not defined in your second code, and it isn't set to anything.

Note that "Target" is only a pre-defined range in some event procedure code, like "Worksheet_BeforeDoubleClick", i.e.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

But the code you posted are NOT event procedure VBA codes, they are manual VBA codes.
Event Procedure VBA code MUST follow certain criteria, such as:
1. It MUST be in one of the pre-defined "ThisWorkbook" or "Sheet" modules. If you place it in a General Module, it will not work.
2. It MUST have one the specific pre-defined names/header-row, like:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

You CANNOT change the name in this header row, or else it will NOT work automatically.

I think it might be better if you show us some sample data, and your expected results.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[somers]

Hi Joe4,

Thank you for your reply and your expert eye on it.
To be honest the code is part of a larger code, i quickly took it out to not confuse matters but seems like i did.

- The code is written in the Sheet module, so not in a general module.
- Topline (Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) is present.

My problem is that if i use cell references in the code - like in example 1 - the return for VALUEHIDEROW is correct.
If i use the target reference in the code - like in example 2 - the return vor VALUEHIDEROW is not correct.

 

[somers]

Seems like my problem lied in my offset, it's fixed now

Solution:
.offset(x) is row offset, i need column offset --> .offset (0,x) did the trick

 

[Joe4]

Solution:
.offset(x) is row offset, i need column offset --> .offset (0,x) did the trick

Note that the format of OFFSET is OFFSET(row, column)
It looks like in your original code you were offsetting rows. Now it looks like you are offsetting columns.