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

[Dan2k]

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.

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.

 

[jolivanes]

Just let us know what the restrictions are.
In other words, why are you making these keystrokes.

 

[Dan2k]

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.

 

[Artik]

Try it

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

 

[Dan2k]

Try it

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

 

[Artik]

Another way to calculate the range you are interested in plus formatting.

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

 

[Dan2k]

Another way to calculate the range you are interested in plus formatting.

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