Multiple Selection of column cells based on contents of another cell in the row

Smudge.Smith · Oct 7th 2020

Hello

Hoping someone can help me with this.

I have been playing with a VBA routine that allows a person to select multiple rows in a sheet. (Not my work - I 'liberated' it from a you tube video).

The routine simply allows you to select a cell in column A and it will place a tick in the box and highlight the row with a colour using conditional formatting. Similarly clicking the same cell unticks the box and removes the highlighting colour.

This works fine if you only want to make one or two selections but becomes tedious if you want to select a lot of rows.

So, what I would like to do is be able to 'select all' by clicking Cell A1 and have it select all those rows that have an email address in the corresponding row in column R.

Similarly, clicking A1 again deselects everything.

Ideas please?

Many thanks

**royUK** · Oct 7th 2020

Replace your code with

Code

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
If Not Target.Address = "$A$1" Then Exit Sub
Dim rRng As Range


On Error Resume Next
Set rRng = Columns(18).SpecialCells(xlCellTypeConstants)
On Error GoTo 0
Select Case Target.Value
Case "All"
rRng.Offset(, -17).Value = "ü"
Case Else: If rRng.Offset(, -17).Value = "ü" Then rRng.Offset(, -17).ClearContents
End Select
End Sub

Display More

You might want to use a Data validation list to allow users to select All or None

Smudge.Smith · Oct 7th 2020

Hi Roy

Thank you so much for your help with this. With the drop down menu it works as advertised so I have no complaints at all and I really appreciate it.

However I feel that maybe I wasn't clear in what I was asking for.

What I meant was I would like the ability to maintain individual row selection as demonstrated in the attached file, with the addition of the ability to select all or none. Unless I have missed something, your code removes the individual selection ability and replaces it with all or nothing?

So ideally, clicking A1 on or off selects all or nothing... whilst clicking A2.... A103 still individually selects / deselects that row.

**royUK** · Oct 7th 2020

I thought that's what you wanted. Try this

Code

Option Explicit ' don't know what this is, read - http://excel-it.com/option_explicit.htm
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
If Target.Address = "$A$1" Then
Dim rRng As Range
On Error Resume Next
Set rRng = Columns(18).SpecialCells(xlCellTypeConstants)
On Error GoTo 0
Select Case Target.Value
Case "All"
rRng.Offset(, -17).Value = "ü"
Case Else: If rRng.Offset(, -17).Value = "ü" Then rRng.Offset(, -17).ClearContents
End Select
End If
End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Target
If .CountLarge > 1 Or .Address = "$A$1" Then Exit Sub
If Not Intersect(Target, Range("A2:A103")) Is Nothing Then
If .Value = "ü" Then .Value = "" Else .Value = "ü"
.Offset(0, 1).Select
End If
End With
End Sub

Display More

Smudge.Smith · Oct 7th 2020

You sir, are a genius!

Just the job.

Exactly what I'm after.

Many thanks

**royUK** · Oct 8th 2020

Pleased to help.

Smudge.Smith · Oct 9th 2020

Hi Roy, sorry to be a bit of a pain but I've since noticed an anomaly which wasn't apparent to me at the beginning when I was trying to get my head around this.

I've moved the Data Validation selector to it's proper place in my chart (I6) and amended the VBA to suit and all worked well up until I filled in row 6 columns with header titles.

Because the sheet VBA now sees text in (row 6 , column 18), the vba bombs out and I get a run time error 13 mismatch error when I select Clear cells.

I've tried adjusting the row offsets but this just creates issues when selecting the proper row ie will select a row when there is no text (email address) in the corresponding cell in column R.

Also Clear all, doesn't actually clear all ticks. It only clears those ticks that were made using the Select All.

If after Select All I then make additional selections by manually ticking individual cells in column I, these selections are left behind when Clear all is selected.

Can you help please?

Many thanks

Smudge

**royUK** · Oct 9th 2020

I think the manual tick shouldn't work the cell in R is empty, that's why when you run clear all Clear All it is being ignored.

Also, adding the two header rows through the range out. Why do you need the "hidden" row?

Smudge.Smith · Oct 9th 2020

Hi Roy

Thanks for the update. I'm just trying to cover my bases here. The people destined to be using the excel sheet are the type to complain that Clear All doesn't actually clear all when they have clicked additional cells...;-) but I see you have removed that option... which in turn removes that issue.

Also the cells in the 'hidden row' contains text in cells which is referenced by another VBA sub which is irrelevant to what I'm trying to achieve here, but thought I'd include it to help demonstrate if it was impacting on the issue that we are discussing.

I'll go and study it now and see if I can learn something from it.

Again, many thanks for your help.

Smudge

**royUK** · Oct 9th 2020

I haven't removed any option or changed them, it states Clear or Select All.

The amended code will Clear All even when added manually.

Multiple Selection of column cells based on contents of another cell in the row

Participate now!

Share

Users Viewing This Thread