Forum Discussion
Hide blank cells
I am exporting information from excel into another program. I don't want all the info. I used to be able to select the filter in at the top of a column and choose to hide blank cells. Then the info on the rows next to the blank cells would hide. When I copied the info out, I did not have all the extra info. This no longer works for me. How do I do this?
Person's name time spent $
2 $10
1 $5
I want:
Person's name time spent $
1 Reply
1. Use the filter function to hide blank cells: select the data area, click “Data” > “Filter”.
In the drop-down menu of column headings, uncheck “Blank” and click “OK”.
After hiding the blank cells, only the non-blank cells will be shown.
2. Copy the visible cells: select the filtered data area. Press Alt + ; (to select visible cells) and then press Ctrl + C to copy.
Paste to the target location (another program or Excel worksheet).
Use the “Go to Special” function to select a blank cell:
Select the data area, press Ctrl + G and click on “Special”.
Select “Blank” and click “OK”.
Right-click on the selected blank cell and select “Hide Row”.
3. Use VBA script to hide blank cells:
Press Alt + F11 to open the VBA editor and insert the following script:
vba
Sub HideBlankCells()
Dim rng As Range
Set rng = Selection
rng.SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
End Sub
Run the script to hide the rows of blank cells in the selected region.
4. Check the screening settings: to ensure that the screening function works properly, check whether there are other screening conditions or format interference.
