Skip i'r prif gynnwys

Sut i guddio rhesi os yw celloedd gwag mewn colofn?

Os oes gennych ystod o ddata sy'n poblogi rhai celloedd gwag mewn colofn, ac yn awr, rydych chi am guddio'r rhesi sy'n cynnwys y celloedd gwag yn y golofn honno yn awtomatig. A oes unrhyw ffyrdd da o ddatrys y swydd hon yn Excel yn gyflym ac yn hawdd?

Rhesi cuddio awto os celloedd gwag mewn colofn gyda chod VBA

swigen dde glas saeth Rhesi cuddio awto os celloedd gwag mewn colofn gyda chod VBA

Efallai y bydd y cod canlynol yn eich helpu i guddio pob rhes os yw celloedd gwag mewn colofn benodol ar unwaith, ac os byddwch chi'n dileu'r cynnwys celloedd yn y golofn honno, bydd y rhesi'n cael eu cuddio'n awtomatig hefyd. Gwnewch fel a ganlyn:

1. Cliciwch ar y dde wrth y tab dalen rydych chi am ei guddio'r rhesi os oes celloedd gwag mewn colofn, ac yna dewiswch Gweld y Cod o'r ddewislen cyd-destun, yn y popped allan Microsoft Visual Basic ar gyfer Ceisiadau ffenestr, copïwch a gludwch y cod canlynol i'r gwag Modiwlau:

Cod VBA: Rhesi cuddio awto os yw celloedd gwag mewn colofn:

Private Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice
    Dim xRg As Range
    Application.ScreenUpdating = False
        For Each xRg In Range("A1:A20")
            If xRg.Value = "" Then
                xRg.EntireRow.Hidden = True
        
            Else
                xRg.EntireRow.Hidden = False
            End If
        Next xRg
    Application.ScreenUpdating = True
End Sub

rhesi cuddio auto doc 1

Nodyn: Yn y cod uchod, A1: A20 yw'r rhestr ddata sy'n cynnwys y celloedd gwag rydych chi am eu cuddio'n awtomatig.

2. Yna ewch yn ôl i'r daflen waith, ac yn awr, pan fyddwch chi'n clicio ddwywaith ar unrhyw gell ac yn pwyso Rhowch allwedd, mae'r rhesi sy'n cynnwys celloedd gwag yng ngholofn A wedi'u cuddio ar unwaith, ac os byddwch chi'n clirio unrhyw gynnwys celloedd yng nghelloedd penodedig colofn A, bydd y rhesi'n cael eu cuddio'n awtomatig.

rhesi cuddio auto doc 2

 

Offer Cynhyrchiant Swyddfa Gorau

🤖 Kutools AI Aide: Chwyldro dadansoddi data yn seiliedig ar: Cyflawniad Deallus   |  Cynhyrchu Cod  |  Creu Fformiwlâu Personol  |  Dadansoddi Data a Chynhyrchu Siartiau  |  Invoke Swyddogaethau Kutools...
Nodweddion Poblogaidd: Darganfod, Amlygu neu Adnabod Dyblygiadau   |  Dileu Rhesi Gwag   |  Cyfuno Colofnau neu Gelloedd heb Colli Data   |   Rownd heb Fformiwla ...
Super-edrych: VLookup Meini Prawf Lluosog  |   VLookup Gwerth Lluosog  |   VLookup Ar Draws Taflenni Lluosog   |   Edrych Niwlog ....
Rhestr gwympo Uwch: Creu Rhestr Gollwng yn Gyflym   |  Rhestr Gollwng Dibynnol   |  Rhestr Gollwng Aml-ddewis ....
Rheolwr Colofn: Ychwanegu Nifer Penodol o Golofnau  |  Symud Colofnau  |  Toglo Statws Gwelededd Colofnau Cudd  |  Cymharwch Ystodau a Cholofnau ...
Nodweddion dan Sylw: Ffocws ar y Grid   |  Golwg Dylunio   |   Bar Fformiwla Mawr   |  Rheolwr Llyfr Gwaith a Thaflen   |  Llyfrgell Adnoddau (Testun Auto)   |  Dewiswr Dyddiad   |  Cyfuno Taflenni Gwaith   |  Amgryptio/Dadgryptio Celloedd   |  Anfon E-byst trwy Restr   |  Hidlo Super   |   Hidlo Arbennig (hidlo mewn print trwm/italig/strikethrough...) ...
15 Set Offer Gorau:  12 Testun offer (Ychwanegu Testun, Dileu Cymeriadau,...)   |   50 + Siart Mathau (Siart Gantt,...)   |   40+ Ymarferol Fformiwlâu (Cyfrifwch oedran yn seiliedig ar ben-blwydd,...)   |   19 mewnosod offer (Mewnosod Cod QR, Mewnosod Llun o'r Llwybr,...)   |   12 Trosi offer (Rhifau i Eiriau, Trosi arian cyfred,...)   |   7 Uno a Hollti offer (Rhesi Cyfuno Uwch, Celloedd Hollt,...)   |   ... a mwy

Supercharge Eich Sgiliau Excel gyda Kutools ar gyfer Excel, a Phrofiad Effeithlonrwydd Fel Erioed Erioed. Kutools ar gyfer Excel Yn Cynnig Dros 300 o Nodweddion Uwch i Hybu Cynhyrchiant ac Arbed Amser.  Cliciwch Yma i Gael Y Nodwedd Sydd Ei Angen Y Mwyaf...

Disgrifiad

Mae Office Tab yn dod â rhyngwyneb Tabbed i Office, ac yn Gwneud Eich Gwaith yn Haws o lawer

  • Galluogi golygu a darllen tabbed yn Word, Excel, PowerPoint, Cyhoeddwr, Mynediad, Visio a Phrosiect.
  • Agor a chreu dogfennau lluosog mewn tabiau newydd o'r un ffenestr, yn hytrach nag mewn ffenestri newydd.
  • Yn cynyddu eich cynhyrchiant 50%, ac yn lleihau cannoedd o gliciau llygoden i chi bob dydd!

 

Comments (36)
No ratings yet. Be the first to rate!
Rogerio Souza
Rogerio Souza
This comment was minimized by the moderator on the site
Can you help me with a similar problem?

I need to auto hide rows that are completely blank, in a grid like the example.

https://ibb.co/zJW8BKq

In this case, i want to hide just the rows 4, 5, 8, 10 and 13. I cant use this code because it verifies each cell in a column and i need to verifies the entire row, between columns B and I.
Reply
0
0
Yanin Panyavisitkul
Yanin Panyavisitkul
This comment was minimized by the moderator on the site
I already copied the code, but nothing happened in my worksheet, Do I need to change anything with the code?
Thank you.
Reply
0
0
Yanin Panyavisitkul
Yanin Panyavisitkul
This comment was minimized by the moderator on the site
I already copied the code, but nothing happened in my worksheet, Do I need to change anything with the code?
Thank you.
Reply
0
0
Sam
Sam
This comment was minimized by the moderator on the site
I'm trying to find a way hide blank rows in sheet2 from A2:A20, if Sheet!A5=Yes. any suggestion?
Reply
0
0
vianmacnamara
vianmacnamara
This comment was minimized by the moderator on the site
Hi, i'm having issues with the "automation" of the macro. I'm searching for a way for the code to work as an automated process without the needs of "manually" running the macro. i've been using my own variant of your code due to me finding issues to use your code. This is my variant:

Sub HideRows()
Dim xRg As Range
Application.ScreenUpdating = False
For Each xRg In Range("A3:A800")
If (xRg.Value = "") Then
xRg.EntireRow.Hidden = True
Else
xRg.EntireRow.Hidden = False
End If
Next xRg
Application.ScreenUpdating = True
End Sub

I'm trying to create a spreadsheet which requires everchanging values, therefore would require a genuine "automated" procress. I'm relatively new to VBA and if your code actually already meets my requirements would you mind helping/teach me to apply it in VBA? Thanks.
Reply
Report
0
0
skyyang
skyyang     vianmacnamara
This comment was minimized by the moderator on the site
Hello, Vian
In fact, the code in our article can be run automatically.
You must copy and paste the code into the code module of current worksheet, then return to the worksheet, double-click any cell, and press the Enter key, the row containing the blank cell will be is directly hidden.

Please follow the method in this article step by step, hope it can help you!
Thank you!
Reply
Report
0
0
Syafitri Nurjanah
Syafitri Nurjanah
This comment was minimized by the moderator on the site
apakah ada rumus lain?, saya berharap baris kosong itu akan terhapus saat mencetak file menjadi PDF tapi tampilan di excelnya tetap
Reply
0
0
skyyang
skyyang     Syafitri Nurjanah
This comment was minimized by the moderator on the site
Hello, Nurjanah
To solve your problem, first, you should hide the blank rows, and then print the data, after printing the data, you need unhide the blank rows again. Please do as this:
1. Apply this formula: =COUNTA(A2:E2) beside your data, see screenshot:
https://www.extendoffice.com/images/stories/comments/comment-skyyang/doc-hide-blanks-1.png
2. Then, filter the new helper Blank column, hide all 0 value rows, see screenshot:
https://www.extendoffice.com/images/stories/comments/comment-skyyang/doc-hide-blanks-2.png
3. And then, you should hide the new Blank column, and print the data, after printing the worksheet, please cancel the filter to unhide the blank rows as you need.
Please try, hope it can help you!
Reply
Report
0
0
Asaf
Asaf
This comment was minimized by the moderator on the site
When executing the code in office 2013/2019/2021 it works but takes too much time to complete (only 95 rows to hide).
How can you speed this?
Thanks!
Reply
0
0
skyyang
skyyang     Asaf
This comment was minimized by the moderator on the site
Hi, Asaf,
The code works well in my Excel file, could you upload your attachment worbook here if you don't mind? So that we can help to check the problem.

Thank you!
Reply
Report
0
0
I Nyoman Sweta
I Nyoman Sweta
This comment was minimized by the moderator on the site
How to hide row which contain checkbox?
Reply
0
0
skyyang
skyyang     I Nyoman Sweta
This comment was minimized by the moderator on the site
Hello, Sweta

To hide rows with checkbox, the below article may do you a favor:
How To Hide Checkbox When Row Is Hidden In Excel?

Please try, if you still have any other question, please comment here.
Reply
Report
0
0
Cathy
Cathy
This comment was minimized by the moderator on the site
If the value in column E is blank or 0 i would like the row to auto hide. If the value in E changes to something other than blank or 0 I would like it to show. Report is 1500 rows
Reply
0
0
skyyang
skyyang     Cathy
This comment was minimized by the moderator on the site
Hello, Cathy,
To hide the rows automatically based on blank cells or 0 values, please use the below vba code:
Private Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice
    Dim xRg As Range
    Application.ScreenUpdating = False
        For Each xRg In Range("E1:E1500")
            If (xRg.Value = "") Or (xRg.Value = "0") Then
                xRg.EntireRow.Hidden = True
            Else
                xRg.EntireRow.Hidden = False
            End If
        Next xRg
    Application.ScreenUpdating = True
End Sub


Please try, hope it can help you!
Reply
Report
0
0
Cathy
Cathy
This comment was minimized by the moderator on the site
I would like to auto hide rows if column E is blank or 0
Reply
0
0
There are no comments posted here yet
Load More
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations