Empty cells not being recognized as blank

L

londa_vba

Board Regular
Joined
May 11, 2023
Messages
61
Office Version
  1. 365
Platform
  1. Windows
Hello,
I was trying to run code to remove blank rows. However two rows always remained. I went to the raw CSV import data and did go to special looking for blank cells and saw that a lot of seemingly empty cells were not being identified as blank. Can you help?
A11 and A12 are the problematic ones in this example for down stream processes (cell row position will change depending on the import file)

1687623624887.png
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Sort by date Sort by votes
londa_vba said:
Hello,
I was trying to run code to remove blank rows. However two rows always remained. I went to the raw CSV import data and did go to special looking for blank cells and saw that a lot of seemingly empty cells were not being identified as blank. Can you help?
A11 and A12 are the problematic ones in this example for down stream processes (cell row position will change depending on the import file)

View attachment 94193
Click to expand...
jolivanes said:
And the code you used is:
Click to expand...
Sheets("Tube Labels").Visible = True
Sheets("PLIMS Import").Select
Range("A3:B181").Select
Selection.Copy
Sheets("Tube Labels").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Sheets("PLIMS Import").Select
Range("C3:D181").Select
Selection.Copy
Sheets("Tube Labels").Select
Range("D1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Sheets("PF EXT 1").Select
Range("A3").Select

Sheets("Tube Labels").Select
Range("A3:E181").Select
Dim r As Range, rows As Long, i As Long
Set r = ActiveSheet.Range("A1:E181")
rows = r.rows.Count
For i = rows To 1 Step (-1)
If WorksheetFunction.CountA(r.rows(i)) = 0 Then r.rows(i).delete
Next

Sheets("PF EXT 2").Select
Range("A3").Select


Below is the data formatted that I want to copy (there could be up to six of these)
DATA SET 1
1687625073431.png


DATA SET 2
1687625429340.png

Below is the relevant data copied onto another sheet. When I copy a second set of data and run the code two seemingly empty rows remain.

PASTED HERE
1687625205902.png
 

Attachments

  • 1687625111189.png
    1687625111189.png
    6.3 KB · Views: 3
  • 1687625403136.png
    1687625403136.png
    3.7 KB · Views: 3
Upvote 0
A few things.
Pictures are notoriously difficult to run a macro on to check if a proposed solution works.
The pictures don't tell us if the cells have a space character, a linefeed character or whatever non visible character in it.
Try the "Upload Mini Sheet" instead.
Pretend that the "Quote" button does not exist.
Between the pictures and the quotes, it makes thing terrible cluttered.

Now let us know in a concise explanation what needs to be done.
For instance:
In a sheet with pasted data from a .csv file I would like to delete entire rows if a cell in Column A is empty.
However, some cells appear to be empty but my code does not recognize them to be empty.
explain in detail but keep it concise, in other words, don't write a book about it.
Don't see this as a negative answer, it just makes life bearable if you adhere to the KISS method.

In the meantime, run this macro and see if the two questionable cell/rows are selected.
Code: 
Sub Maybe_This_Way()
Dim lr As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row    '<---- The 1 is for Column A. Change as required
With Range("A2:A" & lr)
    .Replace What:="", Replacement:="xx", LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
                .Replace What:="xx", Replacement:="", LookAt:=xlWhole, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    .SpecialCells(4).EntireRow.Select
End With
End Sub
 
Last edited:
Upvote 1
Solution
By the looks of your pictures, the two cells that you say are not deleting is because the entire row is not empty. Offset cells have a value in them.

See if this comes close to what you have in mind.
Check thouroughly to make sure the right ranges are copied/pasted.
Change references if and where required.

Code: 
Sub Try_So()
Dim c As Range, rngDel As Range
With Sheets("Tube Labels")
    .Visible = True
    .Range("A1:B179").Value = Sheets("PLIMS Import").Range("A3:B181").Value
    .Range("D1:E179").Value = Sheets("PLIMS Import").Range("C3:D181").Value
        For Each c In .Range("A1:A145")
            If WorksheetFunction.CountA(c.EntireRow) = 0 Then
                If rngDel Is Nothing Then Set rngDel = c Else Set rngDel = Union(rngDel, c)
            End If
        Next c
End With
rngDel.EntireRow.Select    '<----- Change the "Select" to "Delete" if it does what you need it to do
End Sub


Oh, please use code tags.
 

Attachments

  • Use Code Tags MrExcel.JPG
    Use Code Tags MrExcel.JPG
    50.2 KB · Views: 5
Upvote 0
@londa_vba try changing
VBA Code: 
    Sheets("PLIMS Import").Select
    Range("A3:B181").Select
    Selection.Copy
    Sheets("Tube Labels").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                           :=False, Transpose:=False
    Application.CutCopyMode = False
to
VBA Code: 
    With Sheets("PLIMS Import").Range("A3:B181")
        Sheets("Tube Labels").Range("A1").Resize(.Rows.Count, .Columns.Count).Value = .Value
    End With

Use the same syntax for your later copy/paste values
 
Last edited:
Upvote 1
jolivanes said:
By the looks of your pictures, the two cells that you say are not deleting is because the entire row is not empty. Offset cells have a value in them.

See if this comes close to what you have in mind.
Check thouroughly to make sure the right ranges are copied/pasted.
Change references if and where required.

Code: 
Sub Try_So()
Dim c As Range, rngDel As Range
With Sheets("Tube Labels")
    .Visible = True
    .Range("A1:B179").Value = Sheets("PLIMS Import").Range("A3:B181").Value
    .Range("D1:E179").Value = Sheets("PLIMS Import").Range("C3:D181").Value
        For Each c In .Range("A1:A145")
            If WorksheetFunction.CountA(c.EntireRow) = 0 Then
                If rngDel Is Nothing Then Set rngDel = c Else Set rngDel = Union(rngDel, c)
            End If
        Next c
End With
rngDel.EntireRow.Select    '<----- Change the "Select" to "Delete" if it does what you need it to do
End Sub


Oh, please use code tags.
Click to expand...
thank you for the forum information and help with the code
I now have the following three separate codes that work as i'd like individually but not when ran together


1.
Code: 
Sub Generate_PF_EXT_1()
'Open PF EXT 1 sheet and paste values from Import to PF EXT 1
    
    Sheets("PF EXT 1").Visible = True
    Sheets("PLIMS Import").Select
    Range("A3:D15").Select
    Selection.Copy
    Sheets("PF EXT 1").Select
    Range("A6").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("PLIMS Import").Select
    Range("E3:F15").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("PF EXT 1").Select
    Range("G6").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
  End Sub

2.
Code: 
    Sub Generate_Tube_Labels()
    'generates tube labels
    
    With Sheets("PLIMS Import").Range("A3:B181")
        Sheets("Tube Labels").Range("A1").Resize(.rows.Count, .Columns.Count).Value = .Value
    End With

    With Sheets("PLIMS Import").Range("C3:D181")
    Sheets("Tube Labels").Range("D1").Resize(.rows.Count, .Columns.Count).Value = .Value
    End With
      
  End Sub

3.
Code: 
Sub Remove_Blank_Spaces_Tube_Labels()
'Remove blank spaces in tube label page

Dim lr As Long
lr = Cells(rows.Count, 1).End(xlUp).Row
With Range("A2:A" & lr)
    .Replace What:="", Replacement:="xx", LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
                .Replace What:="xx", Replacement:="", LookAt:=xlWhole, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    .SpecialCells(4).EntireRow.Select
End With
End Sub



But when I try to apply a master macro via command button code 3 removes the blank spaces on the wrong sheet tab.

Code: 
Sub Master_PF_EXT_Button1()
Call ThisWorkbook.Generate_PF_EXT_1
Call ThisWorkbook.Generate_Tube_Labels
Call ThisWorkbook.Remove_Blank_Spaces_Tube_Labels

End Sub

Could you tell me how to specify which sheet to remove the blank spaces from? It should be removed from the "Tube Labels" sheet but it is currently removing it from the "PF EXT 1" sheet. I think my job is blocking me from downloading the XL2BB to use upload mini-sheet.
 
Upvote 0
You quoted Post #4 as a solution.
Does this mean that your original problem, the two cells that looked empty but apparently were not, is solved?
If so, let us know.
For your next problem, explain in detail in a concise manner what you want to achieve.
Most people prefer to help by questions asked, not by 40+ lines of code that don't do what you want.

Oh, don't quote whole posts. Just clutter we don't need. Refer to Post numbers and names id needed.
 
Upvote 0
What did I forget if you use this macro instead of the 3 you showed in Post #7
Make sure to use it on a copy of the original until you know all is OK.
Code: 
Sub Three_In_One()
Sheets("PF EXT 1").Visible = True
    With Sheets("PF EXT 1")
        .Range("A6").Resize(13, 4).Value = Sheets("PLIMS Import").Range("A3:D15").Value
        .Range("G6").Resize(13, 2).Value = Sheets("PLIMS Import").Range("E3:F15").Value
    End With
    With Sheets("Tube Labels")
        .Range("D1").Resize(179, 2).Value = Sheets("PLIMS Import").Range("C3:D181").Value
  '      .Range("A3:A181").SpecialCells(4).EntireRow.Delete    'Needs changing. Commented out
    End With
End Sub
 
Last edited:
Upvote 0
To rectify the empty cell problem.
Again. macro replaces all three from Post #7
Code: 
Sub Three_In_One_New_Version()
Dim i As Long
Sheets("PF EXT 1").Visible = True
Application.ScreenUpdating = False
    With Sheets("PF EXT 1")
        .Range("A6").Resize(13, 4).Value = Sheets("PLIMS Import").Range("A3:D15").Value
        .Range("G6").Resize(13, 2).Value = Sheets("PLIMS Import").Range("E3:F15").Value
    End With
    With Sheets("Tube Labels")
        .Range("D1").Resize(179, 2).Value = Sheets("PLIMS Import").Range("C3:D181").Value
    For i = 181 To 1 Step -1
            If WorksheetFunction.CountA(.Cells(i, 1).EntireRow) = 0 Then .Cells(i, 1).EntireRow.Delete Shift:=xlUp
    Next i
    End With
Application.ScreenUpdating = True
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

WildBurrow
  • Question
Copy/Paste data to next empty row within a range - WITHOUT shifting subsequent rows up
Replies
0
Views
118
WildBurrow
WildBurrow
C
  • Question
How to Delete "Blank" Cells En Masse So They Are Truly Empty?
2 3
Replies
23
Views
2K
chasfh
C
C
  • Question
ole db CopyFromRecordset skipping cell values
Replies
0
Views
322
Clif McIrvin
C
G
  • Solved
How to keep cells and macro button in one position when rows are cut
Replies
8
Views
1K
GA036872
G
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
414
AndyEd
A

Forum statistics

Threads
1,215,136
Messages
6,123,246
Members
449,093
Latest member
Vincent Khandagale

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top