Can a cell be both blank and not blank ?

Logit · #1 03-29-2025, 11:29 AM

The following will remove all 'empty' cells in your workbook. Test on a COPY OF YOUR WORKBOOK for safety reasons. Test for file size before then after running macro.

Code:

Sub LipoSuction()
'JBeaucaire (8/3/2009)
Dim LR As Long, LC As Long
Dim ws As Worksheet

For Each ws In Worksheets
    LR = ws.Range("A" & ws.Rows.Count).End(xlUp).Row + 1
    LC = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column + 1

    'Clear everything below column A last cell and row 1 last cell
        ws.Range(ws.Cells(1, LC), ws.Cells(ws.Rows.Count, ws.Columns.Count)).Delete
        ws.Range(ws.Cells(LR, 1), ws.Cells(ws.Rows.Count, ws.Columns.Count)).Delete
Next ws

End Sub

MartinGM · #2 03-29-2025, 09:44 PM

Quote:

Originally Posted by Logit

The following will remove all 'empty' cells in your workbook. Test on a COPY OF YOUR WORKBOOK for safety reasons. Test for file size before then after running macro.

Code:

Sub LipoSuction()
'JBeaucaire (8/3/2009)
Dim LR As Long, LC As Long
Dim ws As Worksheet

For Each ws In Worksheets
    LR = ws.Range("A" & ws.Rows.Count).End(xlUp).Row + 1
    LC = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column + 1

    'Clear everything below column A last cell and row 1 last cell
        ws.Range(ws.Cells(1, LC), ws.Cells(ws.Rows.Count, ws.Columns.Count)).Delete
        ws.Range(ws.Cells(LR, 1), ws.Cells(ws.Rows.Count, ws.Columns.Count)).Delete
Next ws

End Sub

Thanks, but that isn't addressing my issue. Your procedure clears cells outside the "used" area. My "problem" cell(s) is within the used area.

See my reply to PECOFLYER

Cheers

Martin

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
IF cell in Col A is populated BUT cell in Col C is blank DELETE ROW	ChrisOK	Excel Programming	7	05-05-2019 09:00 PM
Fill blank cell with value from adjacent cell	kevinbradley57	Excel Programming	2	04-17-2018 08:40 AM
Formulato say if cell is blank do this, if not blank do this.	mbesspiata	Excel	1	01-17-2015 05:02 AM
not showing blank cell	alw	Excel	4	03-13-2014 01:08 PM
How to turn all blank lines into non-blank for legal forms	sieler	Word	3	04-12-2009 01:38 PM