| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Collapse Rows?
I did a search on here and wasn't confident that some of the responses
answered my question. I have a about 400 rows of information. A lot of the row information is blank except for the first two columns A and B. Is there a way I can collapse all the "blank rows", which are the rows that don't have data after column A and B? Thank you. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Collapse Rows?
Do you mean that you want to hide rows or columns? - it's a bit
confusing in your post. If you want to hide rows where cells are empty, then you could apply autofilter to a number of columns and select "non-blanks" from the filter pull-downs. Hope this helps. Pete On Feb 6, 3:14*pm, LiveUser wrote: I did a search on here and wasn't confident that some of the responses answered my question. I have a about 400 rows of information. A lot of the row information is blank except for the first two columns A and B. Is there a way I can collapse all the "blank rows", which are the rows that don't have data after column A and B? Thank you. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Collapse Rows?
No. I do not want to hide to the extent that they are gone until you unhide.
I would like there to be a drop down to view what is not being shown. I want this: A B C D 1 Data Data Data Data 2 Data Data Data Data 3 Data Data Data Data 4 Data Data 5 Data Data 6 Data Data 7 Data Data Data Data 8 Data Data Data Data 9 Data Data Data Data To look like this: A B C D 1 Data Data Data Data 2 Data Data Data Data 3 Data Data Data Data 4- Data Data 7 Data Data Data Data 8 Data Data Data Data 9 Data Data Data Data "Pete_UK" wrote: Do you mean that you want to hide rows or columns? - it's a bit confusing in your post. If you want to hide rows where cells are empty, then you could apply autofilter to a number of columns and select "non-blanks" from the filter pull-downs. Hope this helps. Pete On Feb 6, 3:14 pm, LiveUser wrote: I did a search on here and wasn't confident that some of the responses answered my question. I have a about 400 rows of information. A lot of the row information is blank except for the first two columns A and B. Is there a way I can collapse all the "blank rows", which are the rows that don't have data after column A and B? Thank you. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Collapse Rows?
Sub hiderowsifnodata()
For i = Cells(Rows.Count, "a").End(xlUp).Row To 2 Step -1 If Application.CountA(Rows(i)) < 3 Then Rows(i).Hidden = True Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "LiveUser" wrote in message ... I did a search on here and wasn't confident that some of the responses answered my question. I have a about 400 rows of information. A lot of the row information is blank except for the first two columns A and B. Is there a way I can collapse all the "blank rows", which are the rows that don't have data after column A and B? Thank you. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Collapse Rows?
Don,
1. Will this work if there is always data in columns A and B? 2. This is a summary sheet that I am wanting to hide/collapse blank data. All the data is coming from a Start:End!. If I update another worksheet within Start:End! will that row appear on the summary if it was previously hidden? Thank you... "Don Guillett" wrote: Sub hiderowsifnodata() For i = Cells(Rows.Count, "a").End(xlUp).Row To 2 Step -1 If Application.CountA(Rows(i)) < 3 Then Rows(i).Hidden = True Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "LiveUser" wrote in message ... I did a search on here and wasn't confident that some of the responses answered my question. I have a about 400 rows of information. A lot of the row information is blank except for the first two columns A and B. Is there a way I can collapse all the "blank rows", which are the rows that don't have data after column A and B? Thank you. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Collapse Rows?
You probably want this as the first line to unhide all before the test
Rows.Hidden = False -- Don Guillett Microsoft MVP Excel SalesAid Software "LiveUser" wrote in message ... Don, 1. Will this work if there is always data in columns A and B? 2. This is a summary sheet that I am wanting to hide/collapse blank data. All the data is coming from a Start:End!. If I update another worksheet within Start:End! will that row appear on the summary if it was previously hidden? Thank you... "Don Guillett" wrote: Sub hiderowsifnodata() For i = Cells(Rows.Count, "a").End(xlUp).Row To 2 Step -1 If Application.CountA(Rows(i)) < 3 Then Rows(i).Hidden = True Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "LiveUser" wrote in message ... I did a search on here and wasn't confident that some of the responses answered my question. I have a about 400 rows of information. A lot of the row information is blank except for the first two columns A and B. Is there a way I can collapse all the "blank rows", which are the rows that don't have data after column A and B? Thank you. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Collapse Rows?
Don,
This didn't work. It hid rows that had nothing in them at all. It did nothing to rows with Data in columns A and B. I also want to only do this for a selection. For instance A64:M406. Thank you. "Don Guillett" wrote: You probably want this as the first line to unhide all before the test Rows.Hidden = False -- Don Guillett Microsoft MVP Excel SalesAid Software "LiveUser" wrote in message ... Don, 1. Will this work if there is always data in columns A and B? 2. This is a summary sheet that I am wanting to hide/collapse blank data. All the data is coming from a Start:End!. If I update another worksheet within Start:End! will that row appear on the summary if it was previously hidden? Thank you... "Don Guillett" wrote: Sub hiderowsifnodata() For i = Cells(Rows.Count, "a").End(xlUp).Row To 2 Step -1 If Application.CountA(Rows(i)) < 3 Then Rows(i).Hidden = True Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "LiveUser" wrote in message ... I did a search on here and wasn't confident that some of the responses answered my question. I have a about 400 rows of information. A lot of the row information is blank except for the first two columns A and B. Is there a way I can collapse all the "blank rows", which are the rows that don't have data after column A and B? Thank you. |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Collapse Rows?
My understanding is that col a:b would ALWAYS have data and that you wanted to hide rows where c: beyond were blank.....You said nothing about a specified range. You should ALWAYS state ALL of your requirements so as to NOT waste the time of responders. Please give examples of your data or send me a workbook. Sub hiderowsifnodata() For i = 406 to 64 step -1 If Application.CountA(Rows(i)) < 3 Then Rows(i).Hidden = True Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "LiveUser" wrote in message ... Don, This didn't work. It hid rows that had nothing in them at all. It did nothing to rows with Data in columns A and B. I also want to only do this for a selection. For instance A64:M406. Thank you. "Don Guillett" wrote: You probably want this as the first line to unhide all before the test Rows.Hidden = False -- Don Guillett Microsoft MVP Excel SalesAid Software "LiveUser" wrote in message ... Don, 1. Will this work if there is always data in columns A and B? 2. This is a summary sheet that I am wanting to hide/collapse blank data. All the data is coming from a Start:End!. If I update another worksheet within Start:End! will that row appear on the summary if it was previously hidden? Thank you... "Don Guillett" wrote: Sub hiderowsifnodata() For i = Cells(Rows.Count, "a").End(xlUp).Row To 2 Step -1 If Application.CountA(Rows(i)) < 3 Then Rows(i).Hidden = True Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "LiveUser" wrote in message ... I did a search on here and wasn't confident that some of the responses answered my question. I have a about 400 rows of information. A lot of the row information is blank except for the first two columns A and B. Is there a way I can collapse all the "blank rows", which are the rows that don't have data after column A and B? Thank you. |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Collapse Rows?
Don,
I am sorry. I do not intend to waste anyones time and I do greatly appreciate everyones help on this forum. I have worked with macros before that work by range. I am still fairly a novice working with macros. The macro did not work. If Application.CountA(Rows(i)) < 3 Then Rows(i).Hidden = True Next i It bounces back and forth between If Application.CountA(Rows(i)) and Next i. Do I have to specify within the macro which cells I want or can I highlight anything and the macro will recognize what I selected? "Don Guillett" wrote: My understanding is that col a:b would ALWAYS have data and that you wanted to hide rows where c: beyond were blank.....You said nothing about a specified range. You should ALWAYS state ALL of your requirements so as to NOT waste the time of responders. Please give examples of your data or send me a workbook. Sub hiderowsifnodata() For i = 406 to 64 step -1 If Application.CountA(Rows(i)) < 3 Then Rows(i).Hidden = True Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "LiveUser" wrote in message ... Don, This didn't work. It hid rows that had nothing in them at all. It did nothing to rows with Data in columns A and B. I also want to only do this for a selection. For instance A64:M406. Thank you. "Don Guillett" wrote: You probably want this as the first line to unhide all before the test Rows.Hidden = False -- Don Guillett Microsoft MVP Excel SalesAid Software "LiveUser" wrote in message ... Don, 1. Will this work if there is always data in columns A and B? 2. This is a summary sheet that I am wanting to hide/collapse blank data. All the data is coming from a Start:End!. If I update another worksheet within Start:End! will that row appear on the summary if it was previously hidden? Thank you... "Don Guillett" wrote: Sub hiderowsifnodata() For i = Cells(Rows.Count, "a").End(xlUp).Row To 2 Step -1 If Application.CountA(Rows(i)) < 3 Then Rows(i).Hidden = True Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "LiveUser" wrote in message ... I did a search on here and wasn't confident that some of the responses answered my question. I have a about 400 rows of information. A lot of the row information is blank except for the first two columns A and B. Is there a way I can collapse all the "blank rows", which are the rows that don't have data after column A and B? Thank you. |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Collapse Rows?
You didn't answer the question I posed in the last post. As I said, you may
send the workbook to me if desired. You just posted only two lines. Is that what you used? -- Don Guillett Microsoft MVP Excel SalesAid Software "LiveUser" wrote in message ... Don, I am sorry. I do not intend to waste anyones time and I do greatly appreciate everyones help on this forum. I have worked with macros before that work by range. I am still fairly a novice working with macros. The macro did not work. If Application.CountA(Rows(i)) < 3 Then Rows(i).Hidden = True Next i It bounces back and forth between If Application.CountA(Rows(i)) and Next i. Do I have to specify within the macro which cells I want or can I highlight anything and the macro will recognize what I selected? "Don Guillett" wrote: My understanding is that col a:b would ALWAYS have data and that you wanted to hide rows where c: beyond were blank.....You said nothing about a specified range. You should ALWAYS state ALL of your requirements so as to NOT waste the time of responders. Please give examples of your data or send me a workbook. Sub hiderowsifnodata() For i = 406 to 64 step -1 If Application.CountA(Rows(i)) < 3 Then Rows(i).Hidden = True Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "LiveUser" wrote in message ... Don, This didn't work. It hid rows that had nothing in them at all. It did nothing to rows with Data in columns A and B. I also want to only do this for a selection. For instance A64:M406. Thank you. "Don Guillett" wrote: You probably want this as the first line to unhide all before the test Rows.Hidden = False -- Don Guillett Microsoft MVP Excel SalesAid Software "LiveUser" wrote in message ... Don, 1. Will this work if there is always data in columns A and B? 2. This is a summary sheet that I am wanting to hide/collapse blank data. All the data is coming from a Start:End!. If I update another worksheet within Start:End! will that row appear on the summary if it was previously hidden? Thank you... "Don Guillett" wrote: Sub hiderowsifnodata() For i = Cells(Rows.Count, "a").End(xlUp).Row To 2 Step -1 If Application.CountA(Rows(i)) < 3 Then Rows(i).Hidden = True Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "LiveUser" wrote in message ... I did a search on here and wasn't confident that some of the responses answered my question. I have a about 400 rows of information. A lot of the row information is blank except for the first two columns A and B. Is there a way I can collapse all the "blank rows", which are the rows that don't have data after column A and B? Thank you. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I move the expand/collapse for rows and columns in Excel? | Excel Worksheet Functions | |||
| Outlining - collapse rows based on repeated column value | Excel Discussion (Misc queries) | |||
| Expand/Collapse Rows | Excel Worksheet Functions | |||
| Collapse rows with duplicates | Excel Worksheet Functions | |||
| How do you use the explode and collapse panel to hide rows? | Excel Worksheet Functions | |||
Linear Mode
