none
Adding a 2 X 4 border around specific cells even if rows are hidden using vba RRS feed

  • Question

  • Hello everyone.  I am having a hard time figuring out how to achieve what I am trying to do.  I have a datasheet with various rows of data.  After filtering on certain columns, the data changes.  On any given day, some rows may be visible while others may be hidden.  Regardless, I am trying to create a bordered section on the right of the sheet 2 cells tall by 4 across that would cover cells W5 through Z6.  But if, for example, W6 were hidden, it would then be W5 through Z7.  I want there to always appear to be a 2X4 block of cells with a border around them regardless of hidden rows.  Is this possible using vba?  Thank you so much everyone for reading this post :)

    Robert

    Wednesday, October 21, 2015 4:41 PM

Answers

  • This ended up working for me:

    With ActiveSheet
            HeaderRow = .AutoFilter.Range(1).Row
            LastFilterRow = .Range(Split(.AutoFilter.Range.Address, ":")(1)).Row
            GetFilteredRangeTopRow = .Range(.Rows(HeaderRow + 1), .Rows(Rows.Count)).SpecialCells(xlCellTypeVisible)(1).Row
            ActiveCell.Offset(GetFilteredRangeTopRow, 0).Select
            Do
               ActiveCell.Offset(1, 0).Select
               'MsgBox "1 The active cell is " & ActiveCell.Address
            Loop Until Rows(ActiveCell.Row).Hidden = False
            'MsgBox "2 The active cell is " & ActiveCell.Address
            GetFilteredRangeSecondRow = ActiveCell.Row
            
            'GetFilteredRangeSecondRow = .Range(.Rows(HeaderRow + 2), .Rows(Rows.Count)).SpecialCells(xlCellTypeVisible)(2).Row
        If GetFilteredRangeTopRow = LastFilterRow + 1 Then GetFilteredRangeTopRow = 0
        If GetFilteredRangeSecondRow = LastFilterRow + 1 Then GetFilteredRangeTopRow = 0
    End With

    Set rng = ActiveSheet.Range("W" & GetFilteredRangeTopRow & ":Z" & GetFilteredRangeSecondRow)

    With rng.Borders
            .LineStyle = xlContinuous
            .color = vbBlack
            .Weight = xlThin
    End With

    Range("W" & GetFilteredRangeTopRow).Value = "Formula"
    Range("X" & GetFilteredRangeTopRow).Value = "Selection"
    Range("Y" & GetFilteredRangeTopRow).Value = "Auditor"
    Range("Z" & GetFilteredRangeTopRow).Value = "Processor"

    Range("W" & GetFilteredRangeTopRow & ":Z" & GetFilteredRangeSecondRow).Font.Size = 9
    Range("W" & GetFilteredRangeTopRow & ":Z" & GetFilteredRangeTopRow).Interior.ColorIndex = 17
        
    With rng.Borders
            .LineStyle = xlContinuous
            .color = vbBlack
            .Weight = xlThin
    End With


    Robert

    • Marked as answer by greenthnkr Thursday, October 22, 2015 5:44 PM
    Thursday, October 22, 2015 5:44 PM

All replies

  • Can you use a text box? Those can be configured as a fixed size and to not resize with the cells.I

    <edit> I just re-read your post and I'm guessing a text box is not what you're trying to do.

    Can you explain why a specific sized area of cells is important no matter how you're filtering? What makes this one 2 columnx4 row area so special?

    Does it always start in the same cell or does your starting point move around? If so, how would you know where the top left corner is?

    • Edited by Dogubob Wednesday, October 21, 2015 7:02 PM seeking clarification on the problem
    Wednesday, October 21, 2015 6:59 PM
  • I am creating this for another user, and am trying to make everything look uniform.... at least as uniform as possible.  If I could put that 2x4 box to the right in the same area, then the other user(s) who have to fill it in would always look in the same place.  I have found some other code for what I am trying to do but it doesn't make a lot of sense to me.  For example:

    Function AllVisibleCells() As Range
        Set AllVisibleCells = Range("Q2:Q" & Cells(Rows.Count, "Q").End(xlUp).Row).SpecialCells(xlCellTypeVisible)
    End Function


    How do I choose the first and second visible cells for (example) column Q through T (or what I really need, W through Z)?

    Here's another bit of code I found that might be a clue but I can't figure out how to put it all together.....

    Function GetFilteredRangeTopRow() As Long
      Dim HeaderRow As Long, LastFilterRow As Long
      On Error GoTo NoFilterOnSheet
      With ActiveSheet
        HeaderRow = .AutoFilter.Range(1).Row
        LastFilterRow = .Range(Split(.AutoFilter.Range.Address, ":")(1)).Row
        GetFilteredRangeTopRow = .Range(.Rows(HeaderRow + 1), .Rows(Rows.Count)).SpecialCells(xlCellTypeVisible)(1).Row
        If GetFilteredRangeTopRow = LastFilterRow + 1 Then GetFilteredRangeTopRow = 0
      End With
    NoFilterOnSheet:
    End Function


    • Edited by greenthnkr Thursday, October 22, 2015 12:26 PM
    Thursday, October 22, 2015 12:16 PM
  • I think that last bit of code will work for me if I can figure out how to get it to determine the second visible row in addition to the first.  I thought it would be as simple as changing +1 to +2, but that's not doing it.

    Robert

    Thursday, October 22, 2015 1:04 PM
  • This ended up working for me:

    With ActiveSheet
            HeaderRow = .AutoFilter.Range(1).Row
            LastFilterRow = .Range(Split(.AutoFilter.Range.Address, ":")(1)).Row
            GetFilteredRangeTopRow = .Range(.Rows(HeaderRow + 1), .Rows(Rows.Count)).SpecialCells(xlCellTypeVisible)(1).Row
            ActiveCell.Offset(GetFilteredRangeTopRow, 0).Select
            Do
               ActiveCell.Offset(1, 0).Select
               'MsgBox "1 The active cell is " & ActiveCell.Address
            Loop Until Rows(ActiveCell.Row).Hidden = False
            'MsgBox "2 The active cell is " & ActiveCell.Address
            GetFilteredRangeSecondRow = ActiveCell.Row
            
            'GetFilteredRangeSecondRow = .Range(.Rows(HeaderRow + 2), .Rows(Rows.Count)).SpecialCells(xlCellTypeVisible)(2).Row
        If GetFilteredRangeTopRow = LastFilterRow + 1 Then GetFilteredRangeTopRow = 0
        If GetFilteredRangeSecondRow = LastFilterRow + 1 Then GetFilteredRangeTopRow = 0
    End With

    Set rng = ActiveSheet.Range("W" & GetFilteredRangeTopRow & ":Z" & GetFilteredRangeSecondRow)

    With rng.Borders
            .LineStyle = xlContinuous
            .color = vbBlack
            .Weight = xlThin
    End With

    Range("W" & GetFilteredRangeTopRow).Value = "Formula"
    Range("X" & GetFilteredRangeTopRow).Value = "Selection"
    Range("Y" & GetFilteredRangeTopRow).Value = "Auditor"
    Range("Z" & GetFilteredRangeTopRow).Value = "Processor"

    Range("W" & GetFilteredRangeTopRow & ":Z" & GetFilteredRangeSecondRow).Font.Size = 9
    Range("W" & GetFilteredRangeTopRow & ":Z" & GetFilteredRangeTopRow).Interior.ColorIndex = 17
        
    With rng.Borders
            .LineStyle = xlContinuous
            .color = vbBlack
            .Weight = xlThin
    End With


    Robert

    • Marked as answer by greenthnkr Thursday, October 22, 2015 5:44 PM
    Thursday, October 22, 2015 5:44 PM