Need Help with Sorting a Range on Hidden Columns

Hello all,

I have a trouble with sorting data on hidden columns in the same sheet. I can not sort the data since it is hidden.

I thought that it is caused by Goto method but I also tried this without selecting and it does not fixed. Is there any way to sorting the hidden column on the same sheet? Here is my code below:


Application.Goto Reference:="SiralaGRF1", scroll:=False
ActiveWorkbook.Worksheets("Gösterge Paneli").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Gösterge Paneli").Sort.SortFields.Add Key:=Range( _
"W8:AL8"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Gösterge Paneli").Sort
.SetRange Range("W6:AL8")
.Header = xlYes
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With


Thanks for your help!

Thanks for your interest. Since my data is big, unhiding and hiding columns every time makes it work slowly and inconsistent. I need something different I guess.

Oh, your right I forgot to mention it. All columns are hidden except A:Q , and my reference (SiralaGRF1) that constains my data to be sorted is on the range ("W6:AL8") and sorting will be done according to W8:AL8 .
But i could not sort them with vba when the columns are hidden how did you test it?

Yes, I am using the data for my graph. So I want to hide my data. But when it is on hidden columns, code does not sort it

Here is a example workbook. I assigned macro to formbox. When I hide the graph value (column I:L) code does not work. Actually it does not gives error, code just does not work.

You'r right, let me try to explain my issue more clear.
Sheet 2 is my data page, and I just have different datas based on years on sheet 2.
I want to create a dashboard on sheet 1. I just want to see specific dashboard area. Everything else must be hidden, which means range (columns H:XFD) and (rows 18:1048576) will be hidden.
On the other hand, I have created a graph. And for a better pattern, data must be sorted in descending order. To do so, I replicate my data on sheet 2 to sheet 1. The area is I4:L6 . I want to sort this area. For this purpose I created the macro above and assigned it to listbox. When user click on the listbox for changing the year, data on I4:L6 will be changed and macro will sort the data. Graph takes the data on I4:L6 .
When I don't hide the columns, macro runs perfectly and sorts the data. But if I hide the columns, macro does not sort the data.
To sum up, my purpose is sorting the data which is located on hidden columns. Thanks again for your help.

sort 2017.jpgsort 2018.jpg

It still does not work in my pc, maybe it caused by the version difference. You can see the images above,
on second image (2018) c has the biggest value but it is on the third place. Pattern must had be like 2017.

Anyway, I appreciate for your effort, thanks a lot.

I avoid from unhiding-hiding because my real file is much bigger and if I do it that way, there is about 4 seconds lag on the file. But only way is unhiding and hiding again I guess. Thank you Keebellah