Delete Rows with Blank Values in One Particular Column

Delete rows with blank values in one particular column

 df[!(is.na(df$start_pc) | df$start_pc==""), ]

Retain rows with blank values in one particular column and remove rows with blank values in all other columns

Try drop_na() from tidyr

library(tidyr)
df %>% drop_na(-q4)

# output
   id q1 q2 q3 q4 q5
#2 002  2  2  3  2  2
#4 004  3  1  5 NA  4

Delete rows if there are null values in a specific column in Pandas dataframe

If the relevant entries in Charge_Per_Line are empty (NaN) when you read into pandas, you can use df.dropna:

df = df.dropna(axis=0, subset=['Charge_Per_Line'])

If the values are genuinely -, then you can replace them with np.nan and then use df.dropna:

import numpy as np

df['Charge_Per_Line'] = df['Charge_Per_Line'].replace('-', np.nan)
df = df.dropna(axis=0, subset=['Charge_Per_Line'])

Delete rows IF all cells in selected ROW is BLANK

Loop trough each complete row of selection and check if the count of blank cells matchs the count of all cells in row:

My code:

Dim rng As Range

For Each rng In Selection.Rows
    If Application.WorksheetFunction.CountBlank(rng) = rng.Cells.Count Then rng.EntireRow.Delete
Next rng

After executing code:

The emtpy row is gone

UPDATE:

@VasilyIvoyzha is absolutely right. For each won't work properly on this situation. A better approach would be:

Dim i&, x&, lastRow&
lastRow = Range(Split(Selection.Address, ":")(1)).Row
x = Selection.Rows.Count
For i = lastRow To Selection.Cells(1).Row Step -1
    If WorksheetFunction.Concat(Selection.Rows(x)) = "" Then Rows(i).Delete
    x = x - 1
Next i

This way will delete empty rows on selection, even if they are consecutive.

VBA: Deleting rows that have a blank in a certain column and making a button in Excel

If the blank cells in that column are truly blank and not zero length strings returned by formulas (e.g. "") then SpecialCells can remove them all at once.

Private Sub Button_Click()

    Intersect(Me.Columns("AB"), Me.Cells.SpecialCells(xlCellTypeBlanks)).EntireRow.Delete

End Sub

For your own code, you should step backwards in your loop (Step -1). Further, you should not look at column AB for the last cell. If there were blanks in the last row or rows, they would be ignored.

Drop rows containing empty cells from a pandas DataFrame

Pandas will recognise a value as null if it is a np.nan object, which will print as NaN in the DataFrame. Your missing values are probably empty strings, which Pandas doesn't recognise as null. To fix this, you can convert the empty stings (or whatever is in your empty cells) to np.nan objects using replace(), and then call dropna()on your DataFrame to delete rows with null tenants.

To demonstrate, we create a DataFrame with some random values and some empty strings in a Tenants column:

>>> import pandas as pd
>>> import numpy as np
>>> 
>>> df = pd.DataFrame(np.random.randn(10, 2), columns=list('AB'))
>>> df['Tenant'] = np.random.choice(['Babar', 'Rataxes', ''], 10)
>>> print df

          A         B   Tenant
0 -0.588412 -1.179306    Babar
1 -0.008562  0.725239         
2  0.282146  0.421721  Rataxes
3  0.627611 -0.661126    Babar
4  0.805304 -0.834214         
5 -0.514568  1.890647    Babar
6 -1.188436  0.294792  Rataxes
7  1.471766 -0.267807    Babar
8 -1.730745  1.358165  Rataxes
9  0.066946  0.375640         

Now we replace any empty strings in the Tenants column with np.nan objects, like so:

>>> df['Tenant'].replace('', np.nan, inplace=True)
>>> print df

          A         B   Tenant
0 -0.588412 -1.179306    Babar
1 -0.008562  0.725239      NaN
2  0.282146  0.421721  Rataxes
3  0.627611 -0.661126    Babar
4  0.805304 -0.834214      NaN
5 -0.514568  1.890647    Babar
6 -1.188436  0.294792  Rataxes
7  1.471766 -0.267807    Babar
8 -1.730745  1.358165  Rataxes
9  0.066946  0.375640      NaN

Now we can drop the null values:

>>> df.dropna(subset=['Tenant'], inplace=True)
>>> print df

          A         B   Tenant
0 -0.588412 -1.179306    Babar
2  0.282146  0.421721  Rataxes
3  0.627611 -0.661126    Babar
5 -0.514568  1.890647    Babar
6 -1.188436  0.294792  Rataxes
7  1.471766 -0.267807    Babar
8 -1.730745  1.358165  Rataxes

delete row from Excel where column cell is empty

Select the 'email' column and go Data > Filter > AutoFilter to add a filter to that column. That will add a dropdown menu to the column (a little triangle). From this filter you can choose to show only the empty rows. Then you can select them all and delete by right-clicking on the row label and selecting 'Delete Rows'.

How to delete a blank row with all empty cell and keep others row that have some empty cell?

Add Headers to the columns

Apply filter

Set every column to show blanks only

Delete all rows

Remove filter

If all rows in any column are blank then delete entire column + skip first row

Try this:

Sub deleteColumn ()

    Dim TotalRange As Range, col As Range, x as long
    
    Set TotalRange = ActiveSheet.UsedRange  '<< need to set the range first
    Set TotalRange = TotalRange.Offset(1, 0).Resize(TotalRange.Rows.Count - 1, _
                                                    TotalRange.Columns.Count)
    'loop backwards over the columns in TotalRange
    For x = TotalRange.Columns.Count to 1 step -1
        set col = TotalRange.Columns(c)
        If application.counta(col) = 0 then col.entirecolumn.delete
    next x

End Sub

---

Related Topics