VBA to clear rows based on cell value

[ChrisTag1]

Hi all,
I'm new here and I actually have 2 questions, but we will start with this one as this is the one that I've been trying to find an answer to longest.
I have a table that pulls data from another worksheet based on the date selected. I have made a quick chart to show the data in that table. My problem is my chart is showing the blank cells as well as those that have data. I need my chart to only show those cells that have data, but the table will update with the use of a drop down list. So, Nov 1, I may have 3 rows of data to show in my chart, but Nov 6 I only have 1. Since my knowledge of excel and vba are both minimal, I am hoping someone that knows what they are doing can help me.

Basically, what I am looking for is, if row 3 is blank in my table, then hide rows 3-16. But if the next day chosen I have data in rows 2-5, then only hide rows 6-16.

Not sure this makes sense or not.

Thank you for your help in this.

I did post on another forum, and got a vba code that worked in the file they uploaded for me. But when I tried to copy/paste the code into my own worksheet, it didn't work.
This is the code that was given to me....

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Const SelValAdd = "N28"
Const TabRefAdd = "I28"
Dim WkRg  As Range, Rg As Range, HdRg As Range
    If (Target.Address <> Range(SelValAdd).Address) Then Exit Sub
    Set WkRg = Range(TabRefAdd).CurrentRegion
    Application.ScreenUpdating = False
    WkRg.EntireRow.Hidden = False
    For Each Rg In WkRg.Columns(3).Cells
        If (Len(Rg) = 0) Then
            If (HdRg Is Nothing) Then
                Set HdRg = Rg
            Else
                Set HdRg = Union(HdRg, Rg)
            End If
        End If
    Next Rg
    HdRg.EntireRow.Hidden = True
    Application.ScreenUpdating = True
End Sub

When I copy and pasted, I changed the ranges from "N28" to "F1" and "I28" to "I1". I'm not sure if that's why it wouldn't work or if there is something else I am not doing.

I need this to run in the background as this sheet will eventually be hidden once my workbook is finished.

Sorry for the rambling, hope someone can help, and maybe explain what was done as well so I can learn.

 

[MrExcel]

Hello Chris and welcome to the board. Your first question is the one millionth question asked in this forum.

You want to find the last row with data in column J? Here is some simpler code which might be self explanatory:

Option Explicit
Sub HideRowsNotFilled()
Dim LastRow As Long
Dim FirstRowToHide As Long
Dim RowsToHide As Integer
' Where is Last Row
LastRow = Range("J17").End(xlUp).Row
FirstRowToHide = LastRow + 1
RowsToHide = 16 - FirstRowToHide
' Unhide all rows from last time
Range("J2:J17").EntireRow.Hidden = False
' Hide the rows from this time
If FirstRowToHide < 16 Then Cells(FirstRowToHide, 1).Resize(RowsToHide, 1).EntireRow.Hidden = True
End Sub

The cool trick here is Starting from J17 and pressing Ctrl+Up Arrow to find out where that last row is located.
The old old shortcut key for this is pressing End and then the Up Arrow, hence End(XLUP).Row to find out where the row is located.

Your ultimate code will be more complex because this code operates on the active sheet, but this is a start to see if it makes sense.

 

[MrExcel]

Hello Again Chris
We've been waiting for the one millionth question to appear here, so a lot of my friends are watching this thread. One of those is Jon. I am actually texting with someone else who wants to solve your problem without VBA. But I like the VBA solution. Anyway, the great thing about this board is that people come up with better ways to do things.

My code in the first reply is how I would have solved this when I was new to VBA and I like that code because it is easy for an Excel pro to understand what is going on.

But then I remember seeing some code posted here back in 2001 that uses an even better trick: Go To Special and Blanks.

Here is a shorter way to solve this, channeling Jon von der Heyden (Jon may not have been the first to post this, but I remember his post as the one where I realized it is a great technique).

Sub HowJonWouldSolveThis()
    Range("J2:J17").EntireRow.Hidden = False
    Range("J2:J17").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
End Sub

Two lines of code. The first line unhides everything. The second one finds all of the blanks in J2:J17 and hides those.

When you posted your screenshot, I can't see the sheet name. Let's say the sheet name is Data. Then you would use

Sub HowJonWouldSolveThis()
    Worksheets("Data").Range("J2:J17").EntireRow.Hidden = False
    Worksheets("Data").Range("J2:J17").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
End Sub

Thanks for posting here. Let me know if this is not understandable.

Bill

 

[ChrisTag1]

Hi again,

So I tried the first code, and it didn't do anything. I tried the second code and nothing happened with that either. Is it because my table has a fairly (to me) complex formula in it maybe?
I posted a picture of the code in my worksheet, and also a picture that shows the formula I have in the table in case that is the issue.

I really am trying to do things well above my knowledge and IQ I think. lol I have a workbook with 15 worksheets all linked together somehow, and it is these last 2 worksheets that are really getting me. Considering I have never used excel until a couple months ago, I thought I was doing really well. Now I am stuck on this and have been since before Christmas.

Anyway, when I put the code in, save, click run and close back to excel, nothing seems to happen.

I apologize for not understanding well, it truly does look like a simple formula.

Chris

 

[ChrisTag1]

Hi, I tried the first code again...
VBA Code:
Option Explicit
Sub HideRowsNotFilled()
Dim LastRow As Long
Dim FirstRowToHide As Long
Dim RowsToHide As Integer
' Where is Last Row
LastRow = Range("J17").End(xlUp).Row
FirstRowToHide = LastRow + 1
RowsToHide = 16 - FirstRowToHide
' Unhide all rows from last time
Range("J2:J17").EntireRow.Hidden = False
' Hide the rows from this time
If FirstRowToHide < 16 Then Cells(FirstRowToHide, 1).Resize(RowsToHide, 1).EntireRow.Hidden = True
End Sub

Nothing seemed to happen. So I went ahead and tried "How Jon Would Solve This", and it sort of worked. It hid row 17 only, which I think is because that row is completely blank.
What I need is to hide any rows in my table range of J2:L16, if J2:J16 is blank. I don't know if it's because the dates are in columns I1:I16 or if it is the array formula in my table that is causing the issues.
I really hope someone can help with this.
Thanks All

 

[ChrisTag1]

Another code I tried, that I found on this site is ......
Sub hideblankrows ()
For Each cell In Range("j2:j16")
If cell.Value = "" Or cell.Value = 0 Then cell.EntireRow.Hidden = True
Next cell
End Sub

This code works to an extent. It hides the rows, until I choose a date that only has one row of data (ie Nov 6), then it hides all but 3 rows. Leaving the row with data and 2 blank rows.
If my data changes (ie, I have to add a row of data to Nov1, It only shows the original 3 rows and doesn't add the new one).
I wish there was a way to show you the entire spreadsheet at work, so you could see what is going on, but it's about 20 000kb, so unless I can email it there's no way.

 

[ChrisTag1]

Hello Again Chris
We've been waiting for the one millionth question to appear here, so a lot of my friends are watching this thread. One of those is Jon. I am actually texting with someone else who wants to solve your problem without VBA. But I like the VBA solution. Anyway, the great thing about this board is that people come up with better ways to do things.

My code in the first reply is how I would have solved this when I was new to VBA and I like that code because it is easy for an Excel pro to understand what is going on.

But then I remember seeing some code posted here back in 2001 that uses an even better trick: Go To Special and Blanks.

Here is a shorter way to solve this, channeling Jon von der Heyden (Jon may not have been the first to post this, but I remember his post as the one where I realized it is a great technique).

Sub HowJonWouldSolveThis()
    Range("J2:J17").EntireRow.Hidden = False
    Range("J2:J17").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
End Sub

Two lines of code. The first line unhides everything. The second one finds all of the blanks in J2:J17 and hides those.

When you posted your screenshot, I can't see the sheet name. Let's say the sheet name is Data. Then you would use

Sub HowJonWouldSolveThis()
    Worksheets("Data").Range("J2:J17").EntireRow.Hidden = False
    Worksheets("Data").Range("J2:J17").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
End Sub

Thanks for posting here. Let me know if this is not understandable.

Bill

I retried this code (changing the range to "J2:J16" as that is the size of my table) and I received an error message when I tried to run it saying "Error - No Cells Found". I could clearly see the blank cells, so my only conclusion is the macro won't run correctly with a formula in the cell despite the value being "".

Any ideas??

 

[MrExcel]

Thanks for posting the screenshot of the formula. When a formula puts a "" in a cell, it is not seen as a blank cell.
How about this:
Sub hideblankrows ()
For Each cell In Range("j2:j16")
If Len(cell.Value) > 1 then
cell.EntireRow.Hidden = False
Else
cell.EntireRow.Hidden = True
End If
Next cell
End Sub

This will hide anything with "" in the cell and anything where someone cleared out a value by typing a single space.
It still won't deal with the people who clear out a value by mashing the space bar five times.
If you run the code and you still have a cell that appears to be blank, go to an empty cell and type =LEN(J5) (substituting the apparently blank cell for J5). That will tell you if there is something in the cell and how long it might be.

 

[Jon von der Heyden]

Thanks for posting the screenshot of the formula. When a formula puts a "" in a cell, it is not seen as a blank cell.

Hi Bill, hi Chris

1,000,000 questions on I am proud to have contributed 0.2%. And just as I feel as though there are few tricks yet to learn, @Fluff bowls me over with this: Delete Blank Cells and shift cells up code not working

I had no idea that where a cell contains "" (i.e. a nullstring), it can be converted to a true blank using Cell.Value = Cell.Value.

Perhaps worth considering for this solution, assuming converting formulas to constants is OK.