CODEWITHSUNDEEP
vbaexcel
Anna
Anna

Reputation: 43

How to hide rows with variable data?

top part of the worksheetenter image description here

very new to VBA and I'm trying to develop a macro to do some formatting. I have a variable amount of data (row wise, columns are the same) in my worksheet. After the last row of data, there are a bunch of blank white rows, and at the very bottom is a grey-shaded row. I want to hide all of the blank white rows in the middle, so that the grey-shaded row is then right under my last row with data in it.

Here is the code I have so far (note: Column I is the last column). Any help would be greatly appreciated. Right now, I am getting a "type mismatch" error for the "BeforeFinalRow = finalRow - 1" part, but I'm sure there's a lot more that's wrong with this code. Thanks in advance!

    Sub hide_rows()

      Dim BelowUsedData As Long
        BelowUsedData = Cells(Rows.Count, 2).End(xlUp).Row + 1
      Dim RowBelowUsedData As Range
        RowBelowUsedData = Range("A" & BelowUsedData, "I" & BelowUsedData)
        Range("A1").Select
        Selection.End(xlDown).Select
      Dim finalRow As Range
        finalRow = Range(Selection, Selection.End(xlToRight))
      Dim BeforeFinalRow As Long
        BeforeFinalRow = finalRow - 1
        Rng = Range(Cells(RowBelowUsedData, "A"), Cells(BeforeFinalRow, "I")).Select
        Selection.EntireRow.Hidden = True

    End Sub

Upvotes: 0

Views: 719

Answers (3)

urdearboy
urdearboy

Reputation: 14580

You could simplify this and hard code your bottom border cell into the code (Just change the value of BottomBorder in code)

Option Explicit

Sub Test()

Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Sheet1")
Dim LRow As Long, BottomBorder As Long

LRow = ws.Range("B" & ws.Rows.Count).End(xlUp).Offset(1).Row
BottomBorder = 1006   'Change this if your bottom border changes

ws.Range(ws.Cells(LRow, 1), ws.Cells(BottomBorder, 1)).EntireRow.Hidden = True

End Sub

Another option is to use a WorkSheet_Change Event. This will only work if you are inputting data in one entry (row) at a time.

To implement: Hide all unused rows with the exception of 1! So if your last used cell is B4, hide B6 down to BottomBorder which will leave B5 as a white blank row where your next entry will go. Then paste the below code in the worksheet in VBE. Every time an entry is made in your blank row (B5) here, the macro will insert a new row keeping your current format.

This is dynamic so it will also look at the next blank row (After B5, B6 will be your new target row)

Private Sub Worksheet_Change(ByVal Target As Range)

Dim LRow As Long
LRow = Range("B" & Rows.Count).End(xlUp).Offset(1).Row

Application.EnableEvents = False
    If Target.Row = LRow - 1 And Target.Column = 2 Then
        Range("A" & LRow + 1).EntireRow.Insert (xlShiftUp)
    End If
Application.EnableEvents = True

End Sub

Upvotes: 1

jivko
jivko

Reputation: 430

On the photo it looks like the rows are not hidden but grey. The below code will find where the color changes and hide those white rows between the last row with data and the first grey cell:

Sub hide_rows()

Dim rngData As Range
Dim rngFirstCelltoHide As Range
Dim rngLastWhite As Range

Set rngData = Range("B1").CurrentRegion
Set rngFirstCelltoHide = rngData.Cells(rngData.Rows.Count, 1).Offset(1, 0)
Set rngLastWhite = rngFirstCelltoHide

Do Until rngLastWhite.Interior.Color <> rngLastWhite.Offset(1, 0).Interior.Color
    Set rngLastWhite = rngLastWhite.Offset(1, 0)
Loop

Range(rngFirstCelltoHide, rngLastWhite).EntireRow.Hidden = True

End Sub

Upvotes: 1

Wisecrack
Wisecrack

Reputation: 1

finalRow is a range object. That is why you get 'type error' when you subtract 1 from it. Declare the variable as long and assign row number to it as follows: finalRow = Range(Selection, Selection.End(xlToRight)).Row

Upvotes: 0

Related Questions