Sorting Table using Hyperlink

Question

Hi MVP and community members,

Is it possible to click on a hyperlink and make the hyperlink automatically sort my table to display only the data I click?

I don't even know what to Google for since I don't know how to describe the problem.

For example:

I want to create a hyperlink of AW18201, so that when I click on it, the table will only show AW18201 data only.

Regards,

Firdaus Yassy

Answer 1

Hi,

step1

save your file as macros enabled workbook (.xlsm)

step2

based from your sample:

assuming that data is on Sheet1

in row 2 are headers

data from row3

The below vba macro, adds a drop down list in cell G2

Select an item from the list and you have the expected result

How ??

right click on Sheet1 tab, select View Code and paste in the following

[edit..]

Private Sub Worksheet_Change(ByVal Target As Range) 'START HERE ###

' ### 01 Jan. 2019 oAnast ###

Dim ws As Worksheet

Set ws = ActiveSheet

Dim rCel As Range

Set rCel = ws.Range("G2") '<< drop down list

Dim c As New Collection, cItem

Dim L As Long, i As Long

Dim v As Variant

L = ws.Cells(Rows.Count, "B").End(xlUp).Row

On Error Resume Next

For Each cItem In ws.Range("B3:B" & L) ' < < id numbers in col B from row 3

c.Add cItem, cItem

Next

ReDim v(1 To c.Count + 1)

For i = 1 To c.Count

v(i) = c(i)

Next

v(c.Count + 1) = "ALL"

With rCel.Validation

.Delete

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _

Operator:=xlBetween, Formula1:=Join(v, ",")

End With

If Not Intersect(Target, rCel) Is Nothing Then

If rCel.Value = "ALL" Or rCel = "" Then

ws.AutoFilterMode = False

Exit Sub

End If

ws.Range("B2:B" & L).AutoFilter Field:=1, Criteria1:=rCel.Value ' < < AutoF in col B from row 2

End If

End Sub 'END HERE ###

press Alt+Q to close Visual basic

Hi oAnast,

What you give is quite heavy to digest. But thanks anyway.

There are 2 recorded macro in the file attached, which what I want to happen when clicking the hyperlink. Do you know how to assign macro to the hyperlink?

https://drive.google.com/file/d/1R_9...ew?usp=sharing

Answer 2

Hi oAnast,

Thanks for replying. I know the only solution to this is using VBA macro. Too bad I'm not advanced excel user.

Any link I can learn how to do this using vba macro?