VBA to double-click through a range

[jnathan]

I am trying to get my code to double-click through a range in column B. The range is determined based on the where there is a blank cell in column A - see code below.

The issue I'm having is that the double-click works for the first cell within the range but it then jumps to the first cell falling outside the range and contiues to double-click through these subsequent blank cells.

Just by way of info, the purpose of doing this is to automate double-clicks through a range so as to activate a Worksheet_Change routine within the spreadsheet

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)</SPAN></SPAN>
Y = Range("A" & Rows.Count).End(xlUp).Row + 1</SPAN></SPAN>
Z = Range("B" & Rows.Count).End(xlUp).Row</SPAN></SPAN>
Dim i As Integer</SPAN></SPAN>
For i = Y To Z</SPAN></SPAN>
    Cells(i, 2).Select</SPAN></SPAN>
    SendKeys "{F2}", True</SPAN></SPAN>
    SendKeys "{ENTER}", True</SPAN></SPAN>
Next i</SPAN></SPAN>
End Sub</SPAN></SPAN>

Any help would be greatly appreciated

 

[ranman256]

clarify dbl-click. Do mean you dbl-click on a cell and it autofills thru the data?
And what is in the range? dates? numbers?

 

[jnathan]

clarify dbl-click. Do mean you dbl-click on a cell and it autofills thru the data?
And what is in the range? dates? numbers?

I have a Worksheet_Change routine that is activated by a cell in column B being dbl-clicked (it basically conducts a Vlookup but that's beside the point). As there can be quite a number of cells in column B I want to be able to identify the newly populated cells in column B so that, by right-clicking (for example), the entire range is worked through by the macro and each cell within the range is double-clicked so that the Worksheet_Change routine is activated for each cell within the range.

Hope this helps explain things further but let me know if you need more info