LinkBack URL
About LinkBacks
How to hide empty or blank columns/cells?
from this
Untitled A.jpg
to this
Untitled B.jpg
How to hide empty or blank columns/cells?
from this
Untitled A.jpg
to this
Untitled B.jpg
Select column, right-click, Hide. To unhide, select two adjacent columns on each side, right-click, Unhide.
Hi.Originally Posted by bmouse
I forgot to mention that i have about 300 COLUMNS ( & 500 ROWS) & the blanks are scattered all around. So picking the column 1 by 1 is not an option.
The photo attachment was just to illustrate what i mean only.
Last edited by nez329; 11-06-2014 at 06:41 AM.
Do you just need to hide the blanks or delete them? Depending on what kind of data is stored in the non-blank cells, you can get rid of blanks by sorting data.
Hide,delete or filter is fine.
From the photo example, only D, G & I column's have blank cells across the whole rows, so i require only D, G & I to be hidden.
The rest of the rows is to remain as there are data across some rows.
With blank cells scattered across 300 COLUMNS by 500 ROWS, i need a quick method to 'filter' those that have blank cells in all the whole row & hide or delete them.
Thanks
Last edited by nez329; 11-06-2014 at 07:38 AM.
I don't have much experience with VBA, but I managed to come up with this macro. I tried to write down a step by step explanation of what I did, maybe this will help you. Perhaps someone else can have a crack at this.
Thanks bro for the effort you put in for the explanation.
I will test it out if i can do it cause not entirely familiar with marco.
Thanks again.
Either insert a column on the left of your data or enter this formula in a free column after you data on the right.
Assuming the right column: enter and copy down the column. All rows that are blank will return a blank and the others 1.
Formula:
Please Login or Register to view this content.
In the row under the data enter this formula and copy across the width of the data
Formula:Please Login or Register to view this content.
Now, select the data including the column and row with the formulae and sort on the column. This will put all the 0s together and the 1s together. Delete the rows with 0.
Select the data including row under the data with the formula and sort the data (Left to right)
Delete the columns with 0.
This should eliminate all blank rows and columns.
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
Hi bro, base on the 1st step, my Free column on the right is "IX" so i useEither insert a column on the left of your data or enter this formula in a free column after you data on the right.
Assuming the right column: enter and copy down the column. All rows that are blank will return a blank and the others 1.
Formula:Please Login or Register to view this content.
But i got a "circular reference error".
Please Login or Register to view this content.
Any advise on what is wrong?
Depending on your version/region of Excel you might have a different separator so the formula might look like this
=IF(COUNTA(A1:G1)>0;1;"")
However I can confirm that the method presented by newdoverman works perfect, a brilliant solution indeed
@ bmouse thank you for the separator translation and the comment.
@ nez329 You will get a "circular reference" if the formula includes the cell that the formula is in.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks