Mid ka mid ah hawlaha ugu badan ee Excel waa tirtirida safafka maran. Nooc kasta oo xog ah oo aad ku leedahay Excel , waxaa jira marar badan oo aad sidoo kale yeelan doonto farabadan khadadka maran dhammaan faylka.

Haddii aad leedahay kumanaan saf, gacanta lagu tirtiro khadadka maran waa xanuun weyn oo aan macquul ahayn. Habka ugu badan ee lagu tirtiro khadadka maran ee Excel , ugu yaraan sida lagu sheegay internetka, waa in la isticmaalo nooc ka mid ah macro Excel .

Si kastaba ha noqotee, haddii aadan aqoon u lahayn makros, habkani wuxuu noqon karaa mid adag in la hirgeliyo. Sidoo kale, haddii aysan si sax ah u shaqeynin, waxaa laga yaabaa inaadan wax fikrad ah ka haysan sida loo beddelo makro ee faylkaaga gaarka ah ee Excel . Maqaalkan, waxaan ku tusi doonaa dhowr siyaabood oo aad u tirtiri karto safafka bannaan ee Excel iyo faa'iidooyinka iyo khasaarooyinka hab kasta. Ogsoonow in hababkani ay u shaqeyn doonaan nooc kasta oo Excel ah , laga bilaabo Excel 2003 ilaa Excel 2016 iyo wixii ka dambeeya.

Habka 1 - Ku dar tiirka kala-soocidda

Nasiib wanaag, waxaa jira hab fudud oo aad waxtar u leh oo lagu tirtiro khadadka maran iyada oo aan wax macros ah lahayn. Asal ahaan waxay ku lug leedahay kala-soocidda. Waa kan sida aad u sameyso Aynu nidhaahno waxaan ku haynaa xogtan soo socota ee Excel waxaana rabnaa inaan ka takhalusno khadadka maran:

Waxa ugu horreeya ee aan samayn doono waa gelinaya tiir oo nambar si isku xigta. Waxaa laga yaabaa inaad weydiiso sababta aan u doonayno inaan tan samayno? Hagaag, haddii nidaamka safafka uu yahay arrin, marka aan u kala saarno Tiirka A si aan uga takhalusno xadadka bannaan, ma jiri doonto qaab dib loogu soo celiyo safafka sidii ay ahaayeen ka hor kala-soocidda.

Waa kan sida xaashigu u ekaan doono ka hor inta aynaan kala soocin tiirka magaca xayawaanka:

Hadda dooro labada tiir oo guji ribbonka Xogta ee ^(Data)Excel . Dabadeed dhagsii badhanka kala soocida^(Sort) ee ku yaal tab Data .

Si aad u kala soocdo^{( Sort By)} , dooro Tiirka B^{(Column B)} ka dibna dhagsii OK. Ogsoonow inaad rabto inaad kala soocdo tiirka leh qiimayaasha maran. Haddii in ka badan hal tiir ay leeyihiin qiimayaal madhan, kaliya soo qaado mid.

Hadda xogtaadu waa inay sidan hoos u eegtaa. Sida aad arki karto, way fududahay in la tirtiro safafka bannaan sababtoo ah dhammaantood xagga hoose ayay yaalliin:

Marka aad tirtirto safafka, hadda waxaad arki kartaa sababta aan tiirka ugu darnay tirooyinka? Ka hor inta aan la kala soocin, dalabku wuxuu ahaa " Bisad^(Cat) , Eey^(Dog) , Doofaar^(Pig) , iwm.." oo hadda waa " Bear , Bisad^(Cat) , Lo^(Cow) ', iwm...". Markaa marlabaad ku kala sooc tiirka A si aad u hesho dalabkii asalka ahaa.

Aad^(Pretty) u fudud sax? Waxa aan ka helay habkan waa in ay sidoo kale runtii fududahay in la tirtiro safafka leh hal tiir oo leh qiyam madhan halkii safka oo dhan uu faaruqsan lahaa. Haddaba, maxaa dhacaya haddii aad u baahan tahay inaad hubiso tiirar badan halkii aad ka hubin lahayd hal?

Hagaag, adoo isticmaalaya habkan la mid ah, waxaad kaliya ku dari lahayd dhammaan tiirarka ku kala soocida^(Sort) wada hadalka. Waa tan tusaale kale oo aan abuuray:

Sida aad arki karto, xaashidani aad bay u dhib badan tahay. Hadda waxaan haystaa dhawr saf oo gebi ahaanba madhan, laakiin dhawr saf oo qayb uun bannaan. Haddii aan ku kala saaro tiirka B, ma heli doono dhammaan safafka madhan xagga hoose. Ka dib marka lagu daro tirooyinka, dooro dhammaan shanta tiir, oo guji " Sort " , waxaad arki kartaa waxaan ku daray afar heer oo kala soocida^(Sort) ikhtiyaarka ah.

Markaad tan sameyso, kaliya safafka halka dhammaan afarta tiir ee aan ku daray ay bannaan yihiin ayaa ka muuqan doona xagga hoose.

Hadda waad tirtiri kartaa safafkaas ka dibna horay u soco oo waxaad isticmaali kartaa safafka intiisa kale si aad ugu soo celiso nidaamkii asalka ahaa ee ay ku jireen.

Habka 2 - Isticmaalka Filters

Si la mid ah isticmaalka nooca shaqada^(Sort) , waxaan sidoo kale isticmaali karnaa ikhtiyaarka Filter . Isticmaalka habkan, uma baahnid inaad ku darto tiir dheeraad ah oo wax la mid ah. Kaliya^(Just) dooro dhammaan tiirarka u baahan in lagu hubiyo meelaha bannaan oo guji badhanka Filter .

Waxaad arki doontaa falaar hoos u dhac ah oo ku xigta cinwaan kasta oo safka madaxa ah. Guji taas ka dibna calaamad ka saar Dhammaan^{(Select All)} oo calaamadee meelaha bannaan^(Blanks) ee hoose.

Hadda ku samee wax la mid ah tiir kasta oo xaashida shaqada ah. Ogsoonow haddii aad gaarto meel ay ka muuqdaan safaf banaan oo keliya, markaas uma baahnid inaad u doorato bannaan mid ka mid ah safafka soo haray maadaama horay loo hubiyay.

Waxaad arki doontaa lambarka safafka bannaan oo lagu iftiimiyay buluug. Hadda kaliya dooro safafkaas, midig-guji oo dooro Delete . Markay tagaan, kaliya dhagsii badhanka Filter markale si aad meesha uga saarto shaandhada oo dhammaan safafkaagii asalka ahaa ee aan maran waa inay dib u soo muuqdaan.

Habka 3 – Tirtir Wax Kasta Banaan

Mid ka mid ah hab kale oo aan rabay in aan sheego ayaa tirtiri doona saf kasta oo leh saf gebi ahaanba madhan ama saf kasta oo leh xitaa hal tiir oo maran. Tani dhab ahaantii maaha mid loogu talagalay qof kasta, laakiin waxay noqon kartaa mid waxtar leh haddii aad rabto inaad tirtirto safafka leh oo keliya xogta qayb ah.

Si taas loo sameeyo, dooro dhammaan xogta xaashida oo taabo furaha F5. Tani waxay kuu keeni doontaa Go To dialog, meesha aad rabto inaad gujiso Special .

Hadda ka dooro liiska bannaan^(Blanks) oo guji OK.

Waxaad arki doontaa dhammaan unugyada bannaan ama safafka ayaa lagu iftiimin doonaa cawl. Hadda tabka Guriga^(Home) , dhagsii Delete ka dibna dooro Delete Sheet Safafka^{(Delete Sheet Rows)} .

Sida aad arki karto, tani waxay tirtiraysaa saf kasta oo leh xitaa hal unug maran tiir kasta.

Tani qof walba faa'iido uma aha, laakiin xaaladaha qaarkood, aad bayna u anfacaysaa. Dooro habka ugu fiican adiga. Ku raaxayso!

How to Delete Blank Lines in Excel

One of the most common tasks in Excel is deleting blank rows. Whatever typе of data you may have in Excel, there are many occasions wherе you will also have a bunch of blank lines throughoυt the file.

If you have thousands of rows, manually deleting blank lines is a major pain and virtually impossible. The most common way to delete blank lines in Excel, at least as mentioned online, is to use some sort of Excel macro.

However, if you’re not familiar with macros, this method can be difficult to implement. Also, if it doesn’t work properly, you may have no idea how to change the macro for your particular Excel file. In this article, I’ll show you a couple of ways you can delete blank rows in Excel and the advantages and disadvantages for each method. Note that these methods will work for any version of Excel, from Excel 2003 all the way to Excel 2016 and beyond.

Method 1 – Add Sorting Column

Luckily, there is a simple and very effective way to delete blank lines without any macros. It basically involves sorting. Here’s how you do it. Let’s say we have the following set of data in Excel and we want to get rid of the blank lines:

The first thing we’ll do is insert a column and number it consecutively. You might ask why we would want to do this? Well, if the order of the rows matters, when we sort Column A to get rid of the blank lines, there will be no way to get the rows back in the order they were before sorting.

Here’s what the sheet should look like before we sort the animal name column:

Now select both columns and click on the Data ribbon in Excel. Then click on the Sort button on the Data tab.

For Sort By, choose Column B and then click OK. Note that you want to sort the column that have the blank values in it. If more than one column has blank values, just pick one.

Now your data should look like this below. As you can see, it’s easy to delete the blank rows because they are all at the bottom:

Once you delete the rows, now you can probably see why we added the column with the numbers? Before sorting, the order was “Cat, Dog, Pig, etc..” and now it’s “Bear, Cat, Cow, etc…”. So just sort again by column A to get the original order back.

Pretty easy right? What I like about this method is that it’s also really easy to delete rows that only have one column with blank values rather than the entire row being blank. So, what if you need to check on multiple columns rather than just one?

Well, using this same method, you would just add all the columns in the Sort by dialog. Here’s another example I created:

As you can see, this sheet is more complicated. Now I have several rows that are completely blank, but several rows that are only partially blank. If I just sort by column B, I won’t get all the completely blank rows at the bottom. After adding the numbers, select all five columns, and clicking on Sort, you can see I added four levels to the Sort by option.

When you do this, only the rows where all four of the columns I added are blank will show up at the bottom.

Now you can delete those rows and then go ahead and resort the rest of the rows to get them back into the original order they were in.

Method 2 – Using Filters

In a similar way to using the Sort function, we can also use the Filter option. Using this method, you don’t have to add in any extra column of anything like that. Just select all the columns that need to be checked for blanks and click on the Filter button.

You’ll see a dropdown arrow appear next to each title in the header row. Click on that and then uncheck Select All and check Blanks at the bottom.

Now do the same thing for each column in the worksheet. Note that if you get to a point where there are only blank rows showing, then you don’t have to select blanks for any of the remaining rows as it’s already checked by default.

You will see the number for the blank rows are highlighted in blue. Now just select those rows, right-click and choose Delete. Once they are gone, just click on the Filter button again to remove the filter and all of your original non-blank rows should reappear.

Method 3 – Delete Any Blanks

One other method I wanted to mention will delete any row that has either a completely blank row or any row that has even a single blank column. This is definitely not for everyone, but it could be useful if you want to delete rows that have only partial data.

To do this, select all the data on the sheet and press the F5 key. This will bring up the Go To dialog, where you want to click on Special.

Now select Blanks from the list and click on OK.

You’ll see all the blank cells or rows will be highlighted in grey. Now on the Home tab, click on Delete and then choose Delete Sheet Rows.

As you can see, this deletes any row that has even a single blank cell in any column.

This isn’t useful for everyone, but in some cases, it’s quite handy also. Choose the method that works best for you. Enjoy!

Related posts

• Sida loo Isticmaalo Tilmaamaha Unugyada Hadalka ee Excel

• Sida loo Geliyo Xaashida Shaqada ee Excel Word Doc

• Sida loo isticmaalo Excel's What-If Analysis

• Sida Loo Hagaajiyo Safka gudaha Excel

• Sida Taariikh ahaan loogu kala saaro Excel

• Isticmaal kiiboodhka si aad u bedesho dhererka safka iyo ballaca tiirka gudaha Excel

• 3 Siyood Oo Unugyada Excel Loogu Kala Qaybiyo

• Ku xidhida Excel iyo MySQL

• Hal-Column iyo Xog-urur badan oo Aasaaska ah oo ku kala soocida xaashida faafinta ee Excel

• Si degdeg ah ugu qor nambarada Word iyo Excel

• Sida Loo Sameeyo Liisaska Hoos-u-dhaca ee Isku Xirka Badan ee Excel

• Sida Loo Geliyo CSV ama TSV Xaashida Shaqada ee Excel

• Sida Loo Mideeyo Unugyada, Tiirarka & Safafka gudaha Excel

• 4 siyaabood oo Excel loogu badalo Google Sheets

• Sida Loo Wadaago Faylka Excel Si Fudud Layskula Shaqeeyo

• Sida loo Isticmaalo Tixraacyada Absolute ee Excel

• U isticmaal Magacyada Range Dynamic ee Excel si aad u Dejiso Dabcida

• Sida Dhakhso Loo Geliyo Safaf Badan gudaha Excel

• Sida Loo Abuuro VBA Macro Ama Script gudaha Excel

• Waa maxay sababta ay tahay inaad u isticmaasho safafka la magacaabay ee Excel