Select blocks of cells using arrows.

[jaeiow]

I have some VBA that will attempt to select cells in a direction with a button press. I would like the left and right buttons to extend the selection. In the code I have left arrow only selecting current - 1 column, but ideally, it would go -1 column until a single column is selected, and then go left). The up and down buttons should go up or down an entire block of cells (separated by an entirely blank row of a table). I need some help with the up arrow or if anyone has done this before, a solution using ActiveCell. Because if a user selects a cell the down array should select the whole current block of cells down to the blank, and proceed to the next block if clicked again. So, in other words, move up or down should be dynamic with cell or range selection. However, that seems difficult to make exceptions for in the current code.

Sub Extend_Selection_Right_Column()
'Extend selection one column to the right

  On Error Resume Next
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Resize(Selection.Rows.Count, Selection.Columns.Count + 1).Select
  On Error GoTo 0
  
End Sub

Sub Move_Selection_Left_Column()
'Moves selection one column to the left

  On Error Resume Next
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Resize(Selection.Rows.Count, Selection.Columns.Count - 1).Select
  On Error GoTo 0

End Sub

Sub Move_Selection_Down_Block()
'Moves selection down one block

  On Error Resume Next
  Dim rng As Range
  Set rng = Selection
    rng(rng.Count).Offset(2, 0).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Resize(Selection.Rows.Count, Selection.Columns.Count).Select
  On Error GoTo 0
  
End Sub

Sub Move_Selection_Up_Block()
'Moves selection up one row block

  On Error Resume Next
  Dim rng As Range
  Set rng = Selection
    rng(rng.Count).Offset(-rng.Count - 1, 0).Select
    Range(Selection, Selection.End(xlUp)).Select
    Selection.Resize(Selection.Rows.Count, Selection.Columns.Count).Select
  On Error GoTo 0
  
End Sub

 

[DanteAmor]

I'm trying to figure out how you have your data and what you want to do.
I will try to illustrate it with the following images.
Assuming cell F6 is selected:

Then press the Left button. The action is to select block 4:

Then press the Up button. The action is to select block 1:

Then press the Right button. The action is to select block 2:

If press again the Right button. The action is to select block 3:

Then press the Down button. The action is to select block 6:

If press again the Down button. The action is to select block 9:

If so, try the following:

Sub select_Right()
  Call select_Block(xlToRight)
End Sub

Sub select_Left()
  Call select_Block(xlToLeft)
End Sub

Sub select_Down()
  Call select_Block(xlDown)
End Sub

Sub select_Up()
  Call select_Block(xlUp)
End Sub

Sub select_Block(direction As Variant)
  ActiveCell.CurrentRegion.End(direction).End(direction).CurrentRegion.Select
End Sub

 

[jaeiow]

That is interesting, but my data is structured in a table. So I have contiguous columns with data, and vertical "blanks" only, where extending selection horizontally is going to be necessary.

Move Right:


Move Down (current code):


The issues with my old code:
Move down doesn't retain the horizontal distance selected. Besides that, I can't extend selection left.

 

[DanteAmor]

I can see in your example that you have blocks of 2 lines and also blocks of more than 2 lines.

Try the following:

Sub x_Right()
  Call Select_Rng(0)
End Sub

Sub x_Left()
  Call Select_Rng(1)
End Sub

Sub x_Down()
  Call Select_Rng(2)
End Sub

Sub x_Up()
  Call Select_Rng(3)
End Sub

Sub Select_Rng(n As Long)
  Dim ini As Long, fin As Long, j As Long, i As Long, col As Long, m As Long
  
  With ActiveCell
    If .Value = "" Then Exit Sub
    ini = .CurrentRegion.Cells(1).Row
    fin = ini + ActiveCell.CurrentRegion.Rows.Count - 1
    i = .CurrentRegion.Rows.Count
    col = .Column
    j = Selection.Columns.Count
  End With
  
  If n = 0 Or n = 1 Then
    If col > n Then Cells(ini, col - n).Resize(i, j + 1).Select
  ElseIf n = 2 Then
    m = fin + 2
  ElseIf n = 3 Then
    m = ini - 2
    If m < 1 Then Exit Sub
  End If
  If n = 2 Or n = 3 Then
    If Cells(m, col) <> "" Then
      Cells(m, col).Activate
      With ActiveCell
        ini = .CurrentRegion.Cells(1).Row
        i = .CurrentRegion.Rows.Count
        col = .Column
      End With
      Cells(ini, col).Resize(i, j).Select
    End If
  End If
End Sub

 

[jaeiow]

This is a very nice function on a test data set, but its not working on the table I am using with filled data columns on the left. Any columns on the left and it does not work properly.

 

[DanteAmor]

This is a very nice function on a test data set, but its not working on the table I am using with filled data columns on the left. Any columns on the left and it does not work properly.

What doesn't work on the left?
It's not enough to say "it does not work properly". You could explain with images where the cursor is, and what you expect from the result.

For example, in a table, it looks like this:

Start, cursor in cell G5:

Then press the Left button. Result:

Then press again the Left button. Result:

Then press again the Left button. Result:

If this is not what you need, then you need to explain much better what you need.

 

[jaeiow]

Multiple columns are on the left before this area of the table with formulas. The data on the left is provided by a Power Query.


The macro is selecting the whole column when pressed.

I was able to create a table of just the right section and had it correctly select the regions.

 

[DanteAmor]

OP:

I need some help with the up arrow or if anyone has done this before, a solution using ActiveCell. Because if a user selects a cell the down array should select the whole current block of cells down to the blank

Post #3:

So I have contiguous columns with data, and vertical "blanks" only

There you only mention that you have a blank row. You never mentioned that you have formulas in the cells. Then the macro won't work, since you don't explain in detail what you have.

 

[jaeiow]

I see, I didn't know fully that the formulas were pertinent, although now I see the issue in my response, and should have figured out what was wrong. I did now set NA() instead of formula ""s, and then replace the #N/A with a true blank in a macro. At first I only marked the. top row of each block (the folder depth integer) as #N/A, but now I will follow this through to the child data