Select blocks of cells using arrows.

jaeiow

Board Regular
Joined
Jun 12, 2022
Messages
59
Office Version
  1. 365
Platform
  1. Windows
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.

VBA 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
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
15,234
Office Version
  1. 2013
Platform
  1. Windows
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:
1657555991836.png


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


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


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


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


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

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


If so, try the following:
VBA Code:
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
 

Attachments

  • 1657556019124.png
    1657556019124.png
    5.9 KB · Views: 1

jaeiow

Board Regular
Joined
Jun 12, 2022
Messages
59
Office Version
  1. 365
Platform
  1. Windows
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.
Capture.PNG


Move Right:
Capture3.PNG

Move Down (current code):
Capturedown.PNG

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

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
15,234
Office Version
  1. 2013
Platform
  1. Windows
I can see in your example that you have blocks of 2 lines and also blocks of more than 2 lines.

1657578457341.png


Try the following:
VBA Code:
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
 
Solution

jaeiow

Board Regular
Joined
Jun 12, 2022
Messages
59
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Dec 3, 2018
Messages
15,234
Office Version
  1. 2013
Platform
  1. Windows
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:
1657589149121.png

Then press the Left button. Result:
1657589208460.png


Then press again the Left button. Result:
1657589286278.png


Then press again the Left button. Result:
1657589373036.png


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

Attachments

  • 1657589332001.png
    1657589332001.png
    11.3 KB · Views: 0

jaeiow

Board Regular
Joined
Jun 12, 2022
Messages
59
Office Version
  1. 365
Platform
  1. Windows
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.
wholeCapture.PNG

The macro is selecting the whole column when pressed.
1657589919979.png


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

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
15,234
Office Version
  1. 2013
Platform
  1. Windows
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

Board Regular
Joined
Jun 12, 2022
Messages
59
Office Version
  1. 365
Platform
  1. Windows
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
 

Forum statistics

Threads
1,175,472
Messages
5,897,650
Members
434,667
Latest member
Ftdsa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top