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

Dan;

I think I can imagine what your data looks like. Try this macro on a copy of the data to see if how I imagine the process is correct,

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) = Cells(rw + 1, 8).End(xlDown).Value

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

Loop

End Sub

You have to start in column E at the top data block that you want to start processing. Using the direct programming reference of Cells(<row>, <column>) allows you to bypass having to select a cell before assigning a value. The Cells(...).End(xlDown) is the equivalent of tapping Ctrl+↓.

Answer 3

Jeeped,

In response to your questions:

1. The values in column H are formatted as numbers, two decimal places.
2. After moving the cursor over three cells from column E, the cursor is in an empty cell tapping Ctrl+▼once places the cursor at the top of the data block, tapping a secon d time places the cursor in the cell on the last row of the block.
3. The reference formula is preferred as the data in column H is dynamic.

4) Yes, each data block is seperated by at least one blank line.

Thanks again for taking the time to help me on this.

Dan

Answer 4

4.  There is a function in VBA called CurrentRegion which can reference islands of cell ranges. Is there at least one completely blank row between the data blocks?

Answer 5

Just have three quick questions:

1. Are the values in column H numbers or text?
2. I'm not sure I understand why you have to tap Ctrl+▼ twice to get the last cell in column H of the data block. Is the first row (where you start in column E) blank in column H?
3. Would you rather have that reference formula (e.g. =H5) or the actual value from column H?

Edit: one more,

4.  There is a function in VBA called CurrentRegion which can reference islands of cell ranges. Is there at least one completely blank row between the data blocks?