473,320 Members | 1,958 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Filter rows on each category and reappear!

5
I have many rows (around 3000) with some columns. Each of them are under a row with only the first (A) column filled.
See below
Category A
1983 15648 156156 416 151 021 987464
156156 416 151 021 987464 151 021
9874 64198 378 15648 156156 416 151
Category B
115648 156156 151 021 987464 151
416 151 021 9874645648 156156 416

....and so on.

I want to be able to click on a row on each category, and automatically filter to the below
Category A
1983 15648 156156 416 151 021 987464
Category B
416 151 021 9874645648 156156 416
By clicking again on each line the Full category should appear. Doable? I tried with checkboxes but couldn't make it
Nov 13 '11 #1
11 1645
NeoPa
32,556 Expert Mod 16PB
You don't explain what you want. An example can be helpful to add to an explanation, but it shouldn't ever be used in place of one like this.

I assume you would like to show only one data row with each category label, but you give no indication of which that row should be. Clearly it's not as simple as just the first row following, but that tells us little.

Is this a question about Excel or Access, as I should explain that positional logic makes no sense within an RDBMS (Access) even if it does in a spreadsheet (Excel).
Nov 14 '11 #2
tsokid
5
Hi NeoPa,
as you said I would like to show the row with the category label, and the row I clicked on. See below:
Category A
1983 15648 156156 416 151 021 987464

Category B
115648 156156 151 021 987464 151
416 151 021 9874645648 156156 416

As you can see all the cells below category B would be as they where. When I click a row of category B then it would show the below:
Category A
1983 15648 156156 416 151 021 987464
Category B
416 151 021 9874645648 156156 416
Nov 22 '11 #3
NeoPa
32,556 Expert Mod 16PB
NeoPa:
Is this a question about Excel or Access, as I should explain that positional logic makes no sense within an RDBMS (Access) even if it does in a spreadsheet (Excel).
I would assume this is an Excel question from what you've said so far, but I did ask the question specifically, so I feel it's quite reasonable to expect an answer before I spend any more time on this.

If I start to explain for one application when you're actually working in the other, then I'm just wasting more of my time unnecessarily.
Nov 22 '11 #4
tsokid
5
Sorry Neopa. It's an excel question...
Nov 22 '11 #5
NeoPa
32,556 Expert Mod 16PB
OK. Right. I will look into this for you, as this isn't that straightforward and needs some VBA code.

While I'm doing that, you can consider whether or not the simple fact that Column B is unused is a good enough indicator of how the Category row is recognised. If any data lines are only one entry wide then they will also be recognised as Category rows.

NB. Due to the high chance of someone clicking on a cell for various other reasons than showing / hiding all bar a specific row, I will design the code to be triggered by a double-click instead.
Nov 22 '11 #6
NeoPa
32,556 Expert Mod 16PB
The code I came up with seems to do the job you were interested in. It also checks for a few unusual situations, so is pretty safe. You need to ensure the data matches everything you've explained though - as the structure of the data is an inbuilt assumption of the code :
Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2.  
  3. Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
  4.                                         Cancel As Boolean)
  5.     Dim lngTop As Long, lngBottom As Long
  6.  
  7.     Cancel = True
  8.     With Range("B" & Target.Row)
  9.         If .Row = 1 Then Exit Sub
  10.         If IsEmpty(.Range("A1")) Then Exit Sub
  11.         If .Offset(RowOffset:=-1).EntireRow.Hidden _
  12.         Or .Offset(RowOffset:=1).EntireRow.Hidden Then
  13.             For lngTop = .Row - 1 To 1 Step -1
  14.                 If Not Rows(lngTop & ":" & lngTop).Hidden Then Exit For
  15.             Next lngTop
  16.             For lngBottom = .Row + 1 To .SpecialCells(xlLastCell).Row + 1
  17.                 If Not Rows(lngBottom & ":" & lngBottom).Hidden Then Exit For
  18.             Next lngBottom
  19.             lngBottom = lngBottom - 1
  20.             Rows(lngTop & ":" & lngBottom).Hidden = False
  21.         Else
  22.             lngTop = .End(xlUp).Row
  23.             If lngTop = 1 _
  24.             Or IsEmpty(Range("B" & lngTop + 1)) Then lngTop = .Row
  25.             lngBottom = .End(xlDown).Row
  26.             If IsEmpty(Range("B" & lngBottom)) _
  27.             Or IsEmpty(Range("B" & lngBottom - 1)) Then lngBottom = .Row
  28.             If lngTop < .Row Then _
  29.                 Rows(lngTop & ":" & .Row - 1).Hidden = True
  30.             If lngBottom > .Row Then _
  31.                 Rows(.Row + 1 & ":" & lngBottom).Hidden = True
  32.         End If
  33.     End With
  34. End Sub
PS. I added a 2003 attachment to test and play with.
Attached Files
File Type: zip TSOKid.Zip (10.0 KB, 76 views)
Nov 22 '11 #7
tsokid
5
hey NeoPa . thanks for everything.
it almost works expect if you click on the last category. it seems to have a problem reappearing the rows.
Nov 25 '11 #8
NeoPa
32,556 Expert Mod 16PB
I was just preparing to tell you how wrong you were when I tested it and found exactly the same problem my end :-D

I'll look into it to see if I can post a resolution shortly.
Nov 25 '11 #9
NeoPa
32,556 Expert Mod 16PB
It seems that Range().SpecialCells(xlLastCell) refers to the bottom-right visible cell of the worksheet rather than its bottom-right cell as I'd expected. I've now replaced this with a reference to UsedRange which fits the bill perfectly. A learning point for me :-)

Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2.  
  3. Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
  4.                                         Cancel As Boolean)
  5.     Dim lngTop As Long, lngBottom As Long, lngEnd As Long
  6.  
  7.     Cancel = True
  8.     With Range("B" & Target.Row)
  9.         If .Row = 1 Then Exit Sub
  10.         If IsEmpty(.Range("A1")) Then Exit Sub
  11.         If .Offset(RowOffset:=-1).EntireRow.Hidden _
  12.         Or .Offset(RowOffset:=1).EntireRow.Hidden Then
  13.             For lngTop = .Row - 1 To 1 Step -1
  14.                 If Not Rows(lngTop & ":" & lngTop).Hidden Then Exit For
  15.             Next lngTop
  16.             lngEnd = CLng(Split(UsedRange.Address, "$")(4))
  17.             For lngBottom = .Row + 1 To lngEnd + 1
  18.                 If Not Rows(lngBottom & ":" & lngBottom).Hidden Then Exit For
  19.             Next lngBottom
  20.             lngBottom = lngBottom - 1
  21.             Rows(lngTop & ":" & lngBottom).Hidden = False
  22.         Else
  23.             lngTop = .End(xlUp).Row
  24.             If lngTop = 1 _
  25.             Or IsEmpty(Range("B" & lngTop + 1)) Then lngTop = .Row
  26.             lngBottom = .End(xlDown).Row
  27.             If IsEmpty(Range("B" & lngBottom)) _
  28.             Or IsEmpty(Range("B" & lngBottom - 1)) Then lngBottom = .Row
  29.             If lngTop < .Row Then _
  30.                 Rows(lngTop & ":" & .Row - 1).Hidden = True
  31.             If lngBottom > .Row Then _
  32.                 Rows(.Row + 1 & ":" & lngBottom).Hidden = True
  33.         End If
  34.     End With
  35. End Sub
Nov 25 '11 #10
tsokid
5
did you uploaded somewhere?
Nov 28 '11 #11
NeoPa
32,556 Expert Mod 16PB
I changed the original attachment (Post #7) to reflect the changes, but all that's changed is the code, which is included in its entirety in the last post (#10).
Nov 28 '11 #12

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: Rich Protzel | last post by:
Hello, So my table contains say 100,000 records, and I need to group the categories in fld1 by the highest count of subcategories. Say fld1 contains categories A, B, C, D, E. All of these...
4
by: Hardy Wang | last post by:
Hi all: I have a DataSet populated with values. How can I return ds.Tables.DefaultView's records from a starting number to a ending number? For example, I have 100 records in the DefaultView, but...
3
by: MrNobody | last post by:
I've read that the expression property for DataColumns is used to "Sets or retrieves the expresssion used to filter rows, calculate the values in a column, or create an aggregate column.". I...
6
by: phonl | last post by:
VB.Net 2005 I have a bound datagrid with a list of names. I want to add a button that will filter the names so that only the names that start with "H" will display. How would I do that? ...
7
by: Barkster | last post by:
I'm trying to list my categories and how many items are in each category. I'm not much on sub queries. How can I combine these two items. I'd like to display the category and the corresponding...
7
by: FrankEBailey | last post by:
I'm not sure if this is a completely dumb question, but please humor me :) I have a table of records, called Records, each of which has a Category_ID that places it in a specific category; the...
1
by: ArunDhaJ | last post by:
Hi, Can we write custom function to filter rows from DataTable Select method? Actually my need is to format the row value from (901) 789 1234<BR>(901) 789 1235<BR>(901) 789 1221 to...
0
by: luciacs | last post by:
Hi, I'm a completely novice in HTML and I'm trying to create a portfolio. I have a pull down menu and each section has more categories, and I want to click to each category, and see a picture on...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.