Finding Blank Cells in a worksheet

[me]

Hello all.

Is there a way to get a simple macro that will find all the blank cells
within a given record in a worksheet and paste all those records into a new
worksheet entitled "blanks"? Is this possible or will it take a lot of
coding? Any advice would be welcomed.

Thanks
chris

 

[Bob Flanagan]

Chris, if what you mean is to find all blanks in a particular column and
then copy the rows for those cells to another worksheet, you can use the
Spreadsheet Assistant to do that. It is available from
http://www.add-ins.com. You would first select the column and then
Assistants, Conditional Select, Select Blank Cells. Next you would run
Assistants, Conditional Select, Select Row if Cell Selected. You can then
copy and paste to a new worksheet.

Bob Flanagan
Macro Systems
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

 

[Wei-Dong XU [MSFT]]

Hi chris,

I think the "record" you mentioned is one range of the worksheet. From you description, I'd suggest you can first copy the range into another new-
created worksheet and then perform some operaton on these cells, for example filtering the cells to pick up the blank cells etc. For your
convenience, I wrote one sample for you with commentary.
'Code begin ----------------------------------------------------
Sub Copy2NewWorksheet()

Dim oOrgRng, oRng As Range
Dim oNewRange, oNewBlankRange As Range
Dim oSht As Worksheet

Set oOrgRng = Sheets("Sheet1").Range("A1:E10")

Set oRng = oOrgRng.SpecialCells(xlCellTypeBlanks)
'if there is no blank cells found, exit this sub directly
If oRng.Count > 0 Then

'Create one new sheet
Set oSht = Sheets.Add
'perhaps you need to check whether there is
'one existing sheet who has the name Blank;
'if so, you may need to assign one new name to
'the new created sheet
oSht.Name = "blanks"

'select the source range
Sheets("Sheet1").Select
oOrgRng.Select

'perform the copy
Application.CutCopyMode = False
Selection.Copy

'select the new created blanks sheet
Sheets("blanks").Select
'select one cell to star the paste
Call Range("A1").PasteSpecial(xlPasteAll)
Set oNewRange = Selection


Set oNewBlankRange = oNewRange.SpecialCells(xlCellTypeBlanks)
' I set the border color of these blankcells to blue
oNewBlankRange.Borders.Color = RGB(0, 0, 255)

'Since the contents from the source range has also
'been copied to the new created sheet, you can
'use the code below to clear the range
oNewRange.ClearContents

End If

End Sub
'Code end ------------------------------------------------------

Please feel free to let me know if you have any further questions.

Best regards,
Wei-Dong Xu
Microsoft Product Support Services
Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[me]

Thanks alot! The code does most of what I want it to do.
I made some slight modifications to it.

'This routine will find the blank cells in a
'worksheet and highlight them in a new worksheet.
'
Public Sub BlankChecker()
'Declare all your variables
Dim oOrgRng, oRng As Range 'range properties
Dim oRngEnd As String 'will be input by user
Dim oSheetName As String 'will be input by user
Dim oNewRange, oNewBlankRange As Range 'new range properties
Dim oSht As Worksheet 'new worksheet with blanks

'Prompt user for Sheet Name
oSheetName = InputBox("Enter Name of Worksheet to inspect.")

'Prompt user for Last Cell
oRngEnd = InputBox("Enter the range of Worksheet to inspect.")

'Construct the area to be checked
Set oOrgRng = Sheets(oSheetName).Range(oRngEnd)

'Now select it
oOrgRng.Select

'Make sure you get the blank cells
Set oRng = oOrgRng.SpecialCells(xlCellTypeBlanks)

'if there are no blank cells found, exit this sub directly


'If there are blank cells, here's the branching logic
If oRng.Count > 0 Then

'Create one new sheet
Set oSht = Sheets.Add

'Name new sheet Blanks;
oSht.Name = "Located Blanks"

'Select the source range
Sheets(oSheetName).Select
oOrgRng.Select

'Perform the copy
Application.CutCopyMode = False
Selection.Copy

'Select the newly created blanks sheet
Sheets("Located Blanks").Select

'Select the cell origin for paste
Call Range("A1").PasteSpecial(xlPasteAll)
Set oNewRange = Selection

Set oNewBlankRange = oNewRange.SpecialCells(xlCellTypeBlanks)

' I set the interior color of these blankcells to blue
oNewBlankRange.Interior.Color = RGB(0, 0, 255)

'The contents from the source range werew copied to the
'new sheet. Now do some housecleaning and clear up your mess
oNewRange.ClearContents

End If

End Sub


Thanks for all your help.

chris

 

[Wei-Dong XU [MSFT]]

Very welcome!

Wei-Dong Xu
Microsoft Product Support Services
Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.