Solved: Resizing a range based on matching values.

[pcarmour]

Hi,
I have 3 columns of data and I am trying to find a way of resizing a selection based on matching cells in column A ie when cell A3 is selected I then need to resize the selection down to A24 (all 1117) and across to C24 giving me A3:C24 selected. Or if A31 is selected It resizes to A31:C61.
Please see attached.
Any help is very much appreciated.


I am working with Windows Home Premium version 6.1.7601 SP 1 Build 7601and Excel version 14.0.6123.5001 (32 bit)

[CodeNinja]

pcarmour,
Try adding the following in the worksheet (selection change). I think it will do what you want... It only works if you select a single cell from col A to C (I figured you would want to be able to click off and un-select).


[VBA]Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rng As Range
Dim sCell As String
Dim upperRow As Integer
Dim lowerRow As Integer


If Target.Cells.Count = 1 Then
If Target.Column <= 3 And Target.Row > 1 Then
sCell = ActiveSheet.Cells(Target.Row, "A")
upperRow = Target.Row
While Cells(upperRow - 1, 1) = sCell And upperRow > 2
upperRow = upperRow - 1
Wend
lowerRow = Target.Row
While Cells(lowerRow + 1, 1) = sCell
lowerRow = lowerRow + 1
Wend
Set rng = Range("A" & upperRow & ":C" & lowerRow)
rng.Select
End If
End If

End Sub[/VBA]


Hope this helps.

CodeNinja

[pcarmour]

Hi CodeNinja,
Yet again you have come to my assistance, thank you.
I am however having a problem with your code as I'm unable to run it. I copied it to a module on the spreadsheet but when I try to run it it just searches for the macro but it can't see it.
I'm obviously doing something wrong, sorry.

[Teeroy]

Hi pcarmour,

The code needs to go into a sheet module, not a standard code module, and it is event driven and doesn't need to be "Run".

[pcarmour]

Hi TeeRoy,
Thank you for your comment. My problem now is that I need to incorporate CodeNijina's code into an existing code that runs in a standard module, Is that possible?

[CodeNinja]

PCArmour,
If you want the automatic selection when clicking on a cell, you will need to use the worksheet_selectionChange event. You could put the code in a module and call the code upon activation of the worksheet change event, but you MUST have something in the event or it will not work when you click on different cells.

Attached is the same code above, just in the document in the proper place.

Good luck.

[pcarmour]

HiCodeNinja, OK, got it, brilliant, thank you. It certainly does what I want, Ijust need to play around with it now. This is part of my effort to resolve mythread ‘Match 2 fields and paste 2values’ that unfortunately has not been answered. So your code here now selects the range for the second value to search. Getting there slowly and thanks to you I am making progress.

[pcarmour]

Hi CodeNija, Just thought I'd show you where I have got to. You will see that the program selects the correct item in columns A-C and enters data from K-N beside it with a simple code in Column J. I have now been trying to get this to repeat/loop which should be quite easy but after trying the do while or loop until codes that I know it wont repeat. Can you please let me know how this can be achieved.
Thank you in advance yet again.

[pcarmour]

Hi, This isn't actually working right so please don't waste any time on it at the moment.

[CodeNinja]

Peter,
Really not sure what you are trying to accomplish here. Once you have the data in cols a-c selected, what do you want to do?

[pcarmour]

Hi CodeNija,
Thanks again for continuing with this.
OK here is my latest Test sheet which is working but I think because of the size is extreemly slow. Clicking button 2 searches for the value K2 in the named range 'Holdings' (Col A) , your macro in sheet 1 then triggers and sets a range for all cells with the K2 value, it then searches for the value in L2 within that range.
Once it has found the correct 2 values it pastes the contents of K2:N2 onto the cells on the right F:I.
K2:N2 is deleted and cells move up and the programme then repeats/loops until K2 is empty. As I say this is very slow but as I will only require it to run once this Saturday I'm not too bothered.

[pcarmour]

Hi CodeNinja,
Just to let you know that the programmr was a success. I discovered that the formating was the cause of the very slow running once removed it ran well.
I have attached the final version, clicking run all executes the programme.
Thanks again for all your assistance.