Record macro using Ctrl+arrow key doesn't work

Question

I am trying to record a macro using the Ctrl+ down arrow to move the next cell within a coluumn that contains data.  I am doing this because the range between data populated cells is variable, in other words, the next cell that contains data may be 5 rows down or it may be 15 rows down.

I am also using relative values when I begin the macro recording. 

The problem I am having is that when I run the macro instead of moving down to the next cell populated with data, it will move down the exact number of cells down that was captured in the recording.

Any ideas on how I accomplish what I have described here?

Thanks,

Dan

Answer 1

It's a small matter to change that macro so that it uses a formula to reference the last cell in column H within the data block.

Sub mcr_BottomH_to_TopE()

Dim rw As Long

If Selection.Column <> 5 Then

MsgBox "Select the top value in column E to start."

Exit Sub

End If

rw = Selection.Row

Do While rw < ActiveSheet.UsedRange.Rows.Count

Cells(rw, 5).Formula = Chr(61) & Cells(rw + 1, 8).End(xlDown).Address

rw = Cells(rw + 1, 8).End(xlDown).Offset(1, -3).End(xlDown).Row

Loop

End Sub

Chr(61) is just a fancy way of describing an equals sign (i.e. ASCII 0×061). .Address by default returns the cell's absolute address like $H$1. If you prefer a relative address (e.g. H1) then change that to .Address(0,0).

Answer 2

Jeeped,

You are AMAZING!!

That worked like a charm and I am learning some very cool stuff thanks to you.

Thank you again for all of your help.

Dan

Answer 3

Jeeped,

You are right, you have correctly pictured how my data is structured within each each data block.

The only thing remaining to achieve my desired result is to have a link as opposed to the actual value in the target cell.  As it is written, the macro places the actual value of the contents of "H:last cell in the block" into "E:first cell in the block" instead of a link "=H:last cell in the block".  I wish I could understand your code to the extent that I would know what to insert where to make this happen but I'm a real beginner and just learning VBA as I go.

Thank you so much for explaining the code you have written, I want to learn at the same time, experts like you, help me solve my VBA coding challenges.  I could not find very much on-line about the "direct programming reference of cells" so by explaining to me that Cells(...).End(xlDown) is the equivalent of tapping Ctrl+▼ you helped me understand if even just a little bit, what the code is doing & I am grateful for that.

Thanks again for all the help,

Dan