Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
leave one blank row between data in *visible* rows
I have sets of data (variable number of rows) that are separated by one blank
row. I am programmatically going through the data & hiding any rows that do not meet certain criteria. The problem is, after hiding my rows, I am left with several areas that are separated by more than one blank row. Can anyone help to hide these so that only one blank row is showing? I tried to adapt the following to work only on the visible cells, but I understand that offset will not step past hidden cells. Thanks! Sub leaveOneBlankRow() Dim c As Range Dim rng As Range Set rng = Nothing For Each c In Range("a1:a300") If IsEmpty(c) Then If IsEmpty(c.Offset(1, 0)) Then If rng Is Nothing Then Set rng = c.Offset(1, 0) Else Set rng = union(rng, c.Offset(1, 0)) End If End If End If Next c If Not rng Is Nothing Then rng.EntireRow.Hidden = True End If End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
leave one blank row between data in *visible* rows
Hi,
Try this out on a copy of your data. It seems to work. Sub leaveOneBlankRow() Dim c As Range For Each c In Range("a1:a300") If Not IsEmpty(c) And c.EntireRow.Hidden = True Then c.Offset(1, 0).EntireRow.Hidden = True End If Next c End Sub Ken Johnson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
leave one blank row between data in *visible* rows
Thanks for the reply Ken.
Unfortunately, it doesn't work because there could be many hidden rows between each blank. Perhaps I need some sort of loop? Like If IsEmpty(c) then while c.offset (i,0) .... I'll keep trying. Happy New Year! "Ken Johnson" wrote: Hi, Try this out on a copy of your data. It seems to work. Sub leaveOneBlankRow() Dim c As Range For Each c In Range("a1:a300") If Not IsEmpty(c) And c.EntireRow.Hidden = True Then c.Offset(1, 0).EntireRow.Hidden = True End If Next c End Sub Ken Johnson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
leave one blank row between data in *visible* rows
GettingThere wrote:
Thanks for the reply Ken. Unfortunately, it doesn't work because there could be many hidden rows between each blank. Perhaps I need some sort of loop? Like If IsEmpty(c) then while c.offset (i,0) .... I'll keep trying. Happy New Year! "Ken Johnson" wrote: Hi, Try this out on a copy of your data. It seems to work. Sub leaveOneBlankRow() Dim c As Range For Each c In Range("a1:a300") If Not IsEmpty(c) And c.EntireRow.Hidden = True Then c.Offset(1, 0).EntireRow.Hidden = True End If Next c End Sub Ken Johnson Hi, If your original data consisted of blocks of multiple rows each separated from the next block by just one blank row, then the only way that you could end up with the processed blocks being separated by more than one blank row is when whole blocks of data have been hidden. This leads to three types of visible blank rows... 1. those inbetween two hidden rows with data 2. those with hidden data above and visible data below 3. those with visible data above and hidden data below My next attempt with your problem hides the first and second type of visible blank row and leaves the third type as the separating blank row needed... Public Sub HideAllBarOne() Dim I As Long For I = 2 To 299 If (Cells(I - 1, 1).EntireRow.Hidden And _ Cells(I - 1, 1).Value < "") And _ (Cells(I + 1, 1).EntireRow.Hidden And _ Cells(I + 1, 1).Value < "") Then Cells(I, 1).EntireRow.Hidden = True End If If (Cells(I - 1, 1).EntireRow.Hidden And _ Cells(I - 1, 1).Value < "") And _ (Not Cells(I + 1, 1).EntireRow.Hidden And _ Cells(I + 1, 1).Value < "") Then Cells(I, 1).EntireRow.Hidden = True End If Next I End Sub Hope this is a bit more successful. Happy New Year! Ken Johnson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
leave one blank row between data in *visible* rows
Hello Ken,
Your comment: "... then the only way that you could end up with the processed blocks being separated by more than one blank row is when whole blocks of data have been hidden..." ....was right on. I hadn't looked at the problem that way, but as soon as I read that, I realized that all I really needed to do was make sure that I hid the blank row that followed each block of hidden data. This what I ended up doing: Public Sub HideAllButOneTake2() 'work backwards Dim I As Long For I = 299 To 2 Step -1 If Cells(I, 1).EntireRow.Hidden = True Then Cells(I + 1, 1).EntireRow.Hidden = True End If Next I End Sub Thanks for your help - I appreciate it! "Ken Johnson" wrote: GettingThere wrote: Thanks for the reply Ken. Unfortunately, it doesn't work because there could be many hidden rows between each blank. Perhaps I need some sort of loop? Like If IsEmpty(c) then while c.offset (i,0) .... I'll keep trying. Happy New Year! "Ken Johnson" wrote: Hi, Try this out on a copy of your data. It seems to work. Sub leaveOneBlankRow() Dim c As Range For Each c In Range("a1:a300") If Not IsEmpty(c) And c.EntireRow.Hidden = True Then c.Offset(1, 0).EntireRow.Hidden = True End If Next c End Sub Ken Johnson Hi, If your original data consisted of blocks of multiple rows each separated from the next block by just one blank row, then the only way that you could end up with the processed blocks being separated by more than one blank row is when whole blocks of data have been hidden. This leads to three types of visible blank rows... 1. those inbetween two hidden rows with data 2. those with hidden data above and visible data below 3. those with visible data above and hidden data below My next attempt with your problem hides the first and second type of visible blank row and leaves the third type as the separating blank row needed... Public Sub HideAllBarOne() Dim I As Long For I = 2 To 299 If (Cells(I - 1, 1).EntireRow.Hidden And _ Cells(I - 1, 1).Value < "") And _ (Cells(I + 1, 1).EntireRow.Hidden And _ Cells(I + 1, 1).Value < "") Then Cells(I, 1).EntireRow.Hidden = True End If If (Cells(I - 1, 1).EntireRow.Hidden And _ Cells(I - 1, 1).Value < "") And _ (Not Cells(I + 1, 1).EntireRow.Hidden And _ Cells(I + 1, 1).Value < "") Then Cells(I, 1).EntireRow.Hidden = True End If Next I End Sub Hope this is a bit more successful. Happy New Year! Ken Johnson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
leave one blank row between data in *visible* rows
GettingThere wrote: Hello Ken, Your comment: "... then the only way that you could end up with the processed blocks being separated by more than one blank row is when whole blocks of data have been hidden..." ...was right on. I hadn't looked at the problem that way, but as soon as I read that, I realized that all I really needed to do was make sure that I hid the blank row that followed each block of hidden data. This what I ended up doing: Public Sub HideAllButOneTake2() 'work backwards Dim I As Long For I = 299 To 2 Step -1 If Cells(I, 1).EntireRow.Hidden = True Then Cells(I + 1, 1).EntireRow.Hidden = True End If Next I End Sub Thanks for your help - I appreciate it! Hi, That's a neat solution. Thanks for the feedback, it's nice knowing I was of some assistance. Ken Johnson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Leave Blank If No Data | Excel Discussion (Misc queries) | |||
How do i leave the arrow for the drop down list visible in Excel? | Excel Discussion (Misc queries) | |||
Can I use Sort to alphabetize & leave blank row bet. rows (2003) | New Users to Excel | |||
Leave cell blank if no data | Excel Discussion (Misc queries) | |||
Do not show lack of data as a zero on graph lines, leave blank | Charts and Charting in Excel |