VBA - Select a range of cells with keystrokes but don't want to see the cells

Dan2k

New Member
Joined
Aug 18, 2024
Messages
4
Office Version
  1. 2021
Platform
  1. Windows
Hello,

I've been racking my brain with this one for far too long and decided to finally ask for help.

I have a report that will be extracted everyday but the number of rows and colums will never be the same, although the last 3 columns and the bottom row will always be needed. I need to start on cell E2 and then...

Shift ->End->Right
Shift ->Left x 3
Shift ->End->Down

Shift ->Up x 1

If I can do this successfully then I will capture the correct range of cells that I need every time regardless of the rows or columns and the last 3 colums and the bottom row will be left alone.

When I try to do this by recording a macro (below), it always logs the selection as a range e.g. Range("E2:AA2").Select but this will be incorrect if I run it the next day and the columns/rows have all changed.

VBA Code:
Sub M_1()
'
' M_1 Macro
'

'
    Range(Selection, Selection.End(xlToRight)).Select
    Range("E2:AA2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range("E2:AA33").Select
End Sub

Is there a way to make these moves in the same manner (or better) without hardcoding specific cells into the macro?

I'll appreciate any help on this one,

Thanks in advance.
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Just let us know what the restrictions are.
In other words, why are you making these keystrokes.
 
Upvote 0
Just let us know what the restrictions are.
In other words, why are you making these keystrokes.
Hi there,

I'll need to add conditional formatting to a very specific selection on the sheet but the number of columns and rows will change daily so I need a way to capture the same selction via macro even if things have changed.

The only thing I can think of that I know 100% would capture this selection each time is by starting on E2 and then making these keystrokes. It's just that the macro seems to picks up the specific cell selection and not the actual moves I make.

The screenshot may help although I had to hide a few work related details. As you can see, I need to select the area highlighted in blue everytime i.e. select everything except the top row, the bottom row, the first 4 columns and the last 3 columns. This is even if the number of rows and columns were to change daily,

Thanks.
 

Attachments

  • Example.jpg
    Example.jpg
    245.5 KB · Views: 18
Upvote 0
Try it
VBA Code:
Sub AAA()
    Dim rng As Range
    
    Set rng = Range("E2", Range("E2").End(xlToRight).Offset(, -3))
    Set rng = Range(rng, rng.End(xlDown).Offset(-1))
    
    'rng.Select
    
    MsgBox rng.Address & vbLf & _
            "Columns: " & rng.Columns.Count & vbLf & _
            "Rows: " & rng.Rows.Count
End Sub
Artik
 
Upvote 0
Try it
VBA Code:
Sub AAA()
    Dim rng As Range
   
    Set rng = Range("E2", Range("E2").End(xlToRight).Offset(, -3))
    Set rng = Range(rng, rng.End(xlDown).Offset(-1))
   
    'rng.Select
   
    MsgBox rng.Address & vbLf & _
            "Columns: " & rng.Columns.Count & vbLf & _
            "Rows: " & rng.Rows.Count
End Sub
Artik
Hi Artik,

This works great but I just realised that I've kind of hit the same problem again when it comes to adding conditional formatting to this selection due to my 'currently' limited knowlege of VBA 😞

It's awkward to ask but would it be possible to add in 2 sets of conditional formatting to your code?

In the range you just identified for me, would it be possible to add the below -

If the cell value in this range is equal to 0 then format the font colour white

and -

If the cell value in this range is greater than 0 then format the font colour white and fill Green

If you could do this for me I couldn't thank you enough 🤞
 
Upvote 0
Another way to calculate the range you are interested in plus formatting.
VBA Code:
Sub BBB()
    Dim Scope As Range

    With Range("A1").CurrentRegion
        Set Scope = .Offset(1, 4).Resize(.Rows.Count - 2, .Columns.Count - 7)
    End With

    Call FormatScope(Scope)
End Sub


Sub FormatScope(Scope)

    Scope.Parent.Cells.FormatConditions.Delete

    With Scope
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
                              Formula1:="=0"
        With .FormatConditions(.FormatConditions.Count)
            .SetFirstPriority
            .Font.Color = RGB(255, 255, 255)
            .StopIfTrue = False
        End With

        .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
                              Formula1:="=0"
        With .FormatConditions(.FormatConditions.Count)
            .SetFirstPriority
            .Font.Color = RGB(255, 255, 255)
            .Interior.Color = 5296274
            .StopIfTrue = False
        End With
    End With

End Sub
Artik
 
Upvote 1
Solution
Another way to calculate the range you are interested in plus formatting.
VBA Code:
Sub BBB()
    Dim Scope As Range

    With Range("A1").CurrentRegion
        Set Scope = .Offset(1, 4).Resize(.Rows.Count - 2, .Columns.Count - 7)
    End With

    Call FormatScope(Scope)
End Sub


Sub FormatScope(Scope)

    Scope.Parent.Cells.FormatConditions.Delete

    With Scope
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
                              Formula1:="=0"
        With .FormatConditions(.FormatConditions.Count)
            .SetFirstPriority
            .Font.Color = RGB(255, 255, 255)
            .StopIfTrue = False
        End With

        .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
                              Formula1:="=0"
        With .FormatConditions(.FormatConditions.Count)
            .SetFirstPriority
            .Font.Color = RGB(255, 255, 255)
            .Interior.Color = 5296274
            .StopIfTrue = False
        End With
    End With

End Sub
Artik
Thank you! This works perfectly and it's now sat nicely with the rest of the macro and running smoothly.

I'm learning more and more each day but its quite daunting when you realise just how much you don't know compared to others.

I really appreciate this my friend.

Dzięki (y)
 
Upvote 0

Forum statistics

Threads
1,224,811
Messages
6,181,081
Members
453,021
Latest member
Justyna P

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
Back
Top