Skip to main content

Conas sraitheanna a cheilt go huathoibríoch más cealla bána i gcolún?

Má tá raon sonraí agat a líonann roinnt cealla bána i gcolún, agus anois, ba mhaith leat na sraitheanna ina bhfuil na cealla bána sa cholún sin a cheilt go huathoibríoch. An bhfuil aon bhealaí maithe ann chun an post seo a réiteach in Excel go tapa agus go héasca?

Sraitheanna seiche uathoibríoch más cealla bána i gcolún le cód VBA

mboilgeog cheart gorm saighead Sraitheanna seiche uathoibríoch más cealla bána i gcolún le cód VBA

D’fhéadfadh an cód seo a leanas cabhrú leat na sraitheanna go léir a cheilt más cealla bána i gcolún ar leith iad ag an am céanna, agus má scriosann tú an t-ábhar cille sa cholún sin, beidh na sraitheanna i bhfolach go huathoibríoch freisin. Déan mar a leanas le do thoil:

1. Cliceáil ar dheis ag an táb bileog ar mhaith leat na sraitheanna a cheilt go huathoibríoch má tá cealla bána i gcolún, agus ansin roghnaigh Féach an cód ón roghchlár comhthéacs, sa popped amach Microsoft Visual Basic d’Fheidhmchláir fuinneog, cóipeáil agus greamaigh an cód seo a leanas sa bán Modúil:

Cód VBA: Sraitheanna seithí uathoibríoch más cealla bána i gcolún iad:

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

sraitheanna seiche uathoibríoch doc 1

nótaí: Sa chód thuas, A1: A20 Is é seo an liosta sonraí ina bhfuil na cealla bána a theastaíonn uait a cheilt go huathoibríoch.

2. Ansin téigh ar ais chuig an mbileog oibre, agus anois, nuair a chliceálann tú aon chill faoi dhó agus brúigh Iontráil eochair, tá na sraitheanna ina bhfuil cealla bána i gcolún A curtha i bhfolach ag an am céanna, agus má ghlanann tú aon ábhar cille sna cealla sonraithe i gcolún A, beidh na sraitheanna i bhfolach go huathoibríoch.

sraitheanna seiche uathoibríoch doc 2

 

Uirlisí Táirgiúlachta Oifige is Fearr

🤖 Kutools AI Aide: anailís sonraí a réabhlóidiú bunaithe ar: Forghníomhú Chliste   |  Gin Cód  |  Cruthaigh Foirmlí Saincheaptha  |  Anailís a dhéanamh ar Sonraí agus Cairteacha a Ghin  |  Feidhmeanna Kutools a agairt...
Gnéithe Coitianta: Faigh, Aibhsigh nó Aithnigh Dúblaigh   |  Scrios Sraitheanna Bána   |  Comhcheangail Colúin nó Cealla gan Sonraí a Chailleadh   |   Babhta gan Foirmle ...
Cuardaigh Super: Ilchritéir VLookup  |   VLookup Illuachanna  |   VLookup Trasna Ilbhileoga   |   Amharc doiléir ....
Liosta anuas Casta: Go tapa Cruthaigh Liosta Anuas   |  Liosta anuas Cleithiúnach   |  Liosta Buail Isteach Ilroghnacha ....
Bainisteoir Colún: Cuir Líon Sonrach Colún leis  |  Colúin Bog  |  Scoránaigh Stádas Infheictheachta na gColún Ceilte  |  Déan comparáid idir Raonta & Colúin ...
Gnéithe Réadmhaoin: Fócas Eangaí   |  Amharc Dearaidh   |   Barra Mór na Foirmle   |  Leabhar Oibre & Bainisteoir Bileog   |  Leabharlann Acmhainní (Uaththéacs)   |  Piocálaí Dáta   |  Comhcheangail Bileoga Oibre   |  Criptigh/Díchriptigh Cealla   |  Seol Ríomhphost trí Liosta   |  Scagaire Super   |   Scagaire Speisialta (scagaire trom/iodálach/stailc tríd...) ...
Barr 15 Uirlisí:  12 Téacs uirlisí (Cuir Téacs, Bain Carachtair,...)   |   50 + Cairt cineálacha (Cairt Gantt,...)   |   40+ Praiticiúil Foirmlí (Ríomh aois bunaithe ar lá breithe,...)   |   19 Insertion uirlisí (Cuir isteach Cód QR, Ionsáigh Pictiúr ón gCosán,...)   |   12 Tiontú uirlisí (Uimhreacha le Focail, Comhshó Airgeadra,...)   |   7 Cumaisc & Scoilt uirlisí (Sraitheanna Comhcheangail Casta, Cealla Scoilt,...)   |   ... agus eile

Supercharge Do Scileanna Excel le Kutools le haghaidh Excel, agus Éifeachtúlacht Taithí Cosúil Ná Roimhe. Kutools le haghaidh Excel Tairiscintí Níos mó ná 300 Ardghnéithe chun Táirgiúlacht a Treisiú agus Sábháil Am.  Cliceáil anseo chun an ghné is mó a theastaíonn uait a fháil ...

Tuairisc

Tugann Tab Oifige comhéadan Tabbed chuig Office, agus Déan Do Obair i bhfad Níos Éasca

  • Cumasaigh eagarthóireacht agus léamh tabbed i Word, Excel, PowerPoint, Foilsitheoir, Rochtain, Visio agus Tionscadal.
  • Oscail agus cruthaigh cáipéisí iolracha i gcluaisíní nua den fhuinneog chéanna, seachas i bhfuinneoga nua.
  • Méadaíonn do tháirgiúlacht 50%, agus laghdaíonn sé na céadta cad a tharlaíonn nuair luch duit gach lá!

 

Comments (35)
No ratings yet. Be the first to rate!
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
JOlsen2011
JOlsen2011
This comment was minimized by the moderator on the site
This is working great for me, It hides the rows and opens them when my values change based on formulas.  The question I have is my sheet is 104 rows long.  It churns for a bit each time I click a cell.  Any way to speed up the transition when it is activated?  Or is it my computer?Thanks!
Reply
Report
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