Home

to change range of the macro

%3CLINGO-SUB%20id%3D%22lingo-sub-657346%22%20slang%3D%22en-US%22%3Eto%20change%20range%20of%20the%20macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-657346%22%20slang%3D%22en-US%22%3E%3CP%3Ei%20use%20bellow%20macro%20to%20auto%20filter%20my%20data%20on%20%22Stock%20Detail%22%20sheet.%20i%20have%20some%20numbers%20in%20my%20%22ID%22%20sheet%20and%20i%20use%20bellow%20to%20filter%20data%20of%20stock%20detail%20sheet.%20here%20with%20data%20about%20ID%20numbers%20in%20the%20sheet%20%22ID%22%20Column%20%22ID%20info%22%20Cell%20%22D2%22%20.%20how%20can%20i%20set%20the%20range%20doe%20D%20column%20(ID%20Info)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPrivate%20Sub%20Worksheet_BeforeDoubleClick(ByVal%20Target%20As%20Range%2C%20Cancel%20As%20Boolean)%3CBR%20%2F%3ECancel%20%3D%20True%3CBR%20%2F%3EWith%20Worksheets(%22Stock%20Detail%22)%3CBR%20%2F%3E.Select%3CBR%20%2F%3E.Range(%22A4%22).AutoFilter%20Field%3A%3D1%2C%20Criteria1%3A%3DTarget.Value%3CBR%20%2F%3EEnd%20With%3CBR%20%2F%3EEnd%20Sub%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-657346%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAdmin%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20Online%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETraining%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EUser%20Adoption%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-657390%22%20slang%3D%22en-US%22%3ERe%3A%20to%20change%20range%20of%20the%20macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-657390%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F329467%22%20target%3D%22_blank%22%3E%40Ravindu94%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20%2C%3C%2FP%3E%3CP%3Etry%20this%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3E%3CSPAN%3EPrivate%20Sub%20Worksheet_BeforeDoubleClick(ByVal%20Target%20As%20Range%2C%20Cancel%20As%20Boolean)%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3ECancel%20%3D%20True%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EWith%20Worksheets(%22Stock%20Detail%22)%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3E.Select%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3E.Range(%22A%3AD%22).AutoFilter%20Field%3A%3D4%2C%20Criteria1%3A%3DTarget.Value%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EEnd%20With%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EEnd%20Sub%3C%2FSPAN%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3ERegards%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EBernd%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%3CA%20href%3D%22http%3A%2F%2Fwww.vba-tanker.com%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ewww.vba-tanker.com%3C%2FA%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Ravindu94
Contributor

i use bellow macro to auto filter my data on "Stock Detail" sheet. i have some numbers in my "ID" sheet and i use bellow to filter data of stock detail sheet. here with data about ID numbers in the sheet "ID" Column "ID info" Cell "D2" . how can i set the range doe D column (ID Info)

 

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
With Worksheets("Stock Detail")
.Select
.Range("A4").AutoFilter Field:=1, Criteria1:=Target.Value
End With
End Sub

1 Reply

@Ravindu94 

Hi ,

try this

 

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
With Worksheets("Stock Detail")
.Select
.Range("A:D").AutoFilter Field:=4, Criteria1:=Target.Value
End With
End Sub

 

Regards

Bernd

www.vba-tanker.com

Related Conversations