delete blanks - sporadic
 

My dataset is from A2:G56. Each column has a different header. I have
sporadic blank cells throughout the dataset and want to delete them. How do I
delete the blank cells? See sample below: Thanks in advance :)

Principle Interest Fee1 Fee2 Disbursement
xxxx xxxxx xxxxx
xxxxx xxxxxx
xxxx

delete blanks - sporadic
 

Select the range and F5SpecialBlanksOK

EditDeleteShift Up or Left whichever you choose.

Probably up, looking at your example, but xxxxx doesn't give a good read on
values.

Might be an idea to do this on a copy of the worksheet.


Gord Dibben MS Excel MVP

On Tue, 16 Jan 2007 15:29:01 -0800, Alina
wrote:

My dataset is from A2:G56. Each column has a different header. I have
sporadic blank cells throughout the dataset and want to delete them. How do I
delete the blank cells? See sample below: Thanks in advance :)

Principle Interest Fee1 Fee2 Disbursement
xxxx xxxxx xxxxx
xxxxx xxxxxx
xxxx

delete blanks - sporadic
 

Thanks so much - but for some reason, it's not recognizing that there are any
blanks. My dataset is a copy/paste value from another tab that contains
formulas. The formulas say ="" if criteria is not met. Those end up being my
blank cells. I've tried changing the format. Any help would be appreciated.
Thanks

"Gord Dibben" wrote:

Select the range and F5SpecialBlanksOK

EditDeleteShift Up or Left whichever you choose.

Probably up, looking at your example, but xxxxx doesn't give a good read on
values.

Might be an idea to do this on a copy of the worksheet.


Gord Dibben MS Excel MVP

On Tue, 16 Jan 2007 15:29:01 -0800, Alina
wrote:

My dataset is from A2:G56. Each column has a different header. I have
sporadic blank cells throughout the dataset and want to delete them. How do I
delete the blank cells? See sample below: Thanks in advance :)

Principle Interest Fee1 Fee2 Disbursement
xxxx xxxxx xxxxx
xxxxx xxxxxx
xxxx

delete blanks - sporadic
 

Those cells are not blank if they contain a formula.

You can get rid of these formulas and make the cells truly blank then select
them and delete.

Sub Clear_Stuff()
Dim rng As Range
With Selection
For Each rng In Selection
If rng.HasFormula And rng.Value = "" Then
rng.ClearContents
End If
Next rng
.SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp 'or xlToLeft
End With
End Sub

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + r to open Project Explorer.

Find your workbook/project and select it.

Right-click and InsertModule. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run the macro by going to ToolMacroMacros.

You can also assign this macro to a button or a shortcut key combo.


Gord



On Wed, 17 Jan 2007 09:07:02 -0800, Alina
wrote:

Thanks so much - but for some reason, it's not recognizing that there are any
blanks. My dataset is a copy/paste value from another tab that contains
formulas. The formulas say ="" if criteria is not met. Those end up being my
blank cells. I've tried changing the format. Any help would be appreciated.
Thanks

"Gord Dibben" wrote:

Select the range and F5SpecialBlanksOK

EditDeleteShift Up or Left whichever you choose.

Probably up, looking at your example, but xxxxx doesn't give a good read on
values.

Might be an idea to do this on a copy of the worksheet.


Gord Dibben MS Excel MVP

On Tue, 16 Jan 2007 15:29:01 -0800, Alina
wrote:

My dataset is from A2:G56. Each column has a different header. I have
sporadic blank cells throughout the dataset and want to delete them. How do I
delete the blank cells? See sample below: Thanks in advance :)

Principle Interest Fee1 Fee2 Disbursement
xxxx xxxxx xxxxx
xxxxx xxxxxx
xxxx

delete blanks - sporadic
 

It worked! Thank you :) Only thing I did different was delete the 'orxlToLeft
since I only wanted to shift up. Thank you for the link too, will definitely
use it. Would you also happen to know how to stack or list data from multiple
columns into one column - when the number of rows is undetermined? Sorry to
ask so much...been suffering for 3 days over this.

"Gord Dibben" wrote:

Those cells are not blank if they contain a formula.

You can get rid of these formulas and make the cells truly blank then select
them and delete.

Sub Clear_Stuff()
Dim rng As Range
With Selection
For Each rng In Selection
If rng.HasFormula And rng.Value = "" Then
rng.ClearContents
End If
Next rng
.SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp 'or xlToLeft
End With
End Sub

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + r to open Project Explorer.

Find your workbook/project and select it.

Right-click and InsertModule. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run the macro by going to ToolMacroMacros.

You can also assign this macro to a button or a shortcut key combo.


Gord



On Wed, 17 Jan 2007 09:07:02 -0800, Alina
wrote:

Thanks so much - but for some reason, it's not recognizing that there are any
blanks. My dataset is a copy/paste value from another tab that contains
formulas. The formulas say ="" if criteria is not met. Those end up being my
blank cells. I've tried changing the format. Any help would be appreciated.
Thanks

"Gord Dibben" wrote:

Select the range and F5SpecialBlanksOK

EditDeleteShift Up or Left whichever you choose.

Probably up, looking at your example, but xxxxx doesn't give a good read on
values.

Might be an idea to do this on a copy of the worksheet.


Gord Dibben MS Excel MVP

On Tue, 16 Jan 2007 15:29:01 -0800, Alina
wrote:

My dataset is from A2:G56. Each column has a different header. I have
sporadic blank cells throughout the dataset and want to delete them. How do I
delete the blank cells? See sample below: Thanks in advance :)

Principle Interest Fee1 Fee2 Disbursement
xxxx xxxxx xxxxx
xxxxx xxxxxx
xxxx

delete blanks - sporadic
 

Alina

The 'or xlToLeft was put in there just for your choice.

The ' lets Excel know it is part of the code.

A macro to move all columns into one continuous column regardless of number of
rows in each column.

Apologies to BD......can't remember full name(or mine at times)<g

Sub OneColumnV2()
''''''''''''''''''''''''''''''''''''''''''
'Macro to copy columns of variable length'
'into 1 continous column in a new sheet '
'Modified 17 FEb 2006 by BD
''''''''''''''''''''''''''''''''''''''''''
Dim iLastcol As Long
Dim iLastRow As Long
Dim jLastrow As Long
Dim ColNdx As Long
Dim ws As Worksheet
Dim myRng As Range
Dim ExcludeBlanks As Boolean
Dim myCell As Range

ExcludeBlanks = (MsgBox("Exclude Blanks", vbYesNo) = vbYes)
Set ws = ActiveSheet
iLastcol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
On Error Resume Next

Application.DisplayAlerts = False
Worksheets("Alldata").Delete
Application.DisplayAlerts = True

Sheets.Add.Name = "Alldata"

For ColNdx = 1 To iLastcol

iLastRow = ws.Cells(ws.Rows.Count, ColNdx).End(xlUp).Row

Set myRng = ws.Range(ws.Cells(1, ColNdx), _
ws.Cells(iLastRow, ColNdx))

If ExcludeBlanks Then
For Each myCell In myRng
If myCell.Value < "" Then
jLastrow = Sheets("Alldata").Cells(Rows.Count, 1) _
.End(xlUp).Row
myCell.Copy
Sheets("Alldata").Cells(jLastrow + 1, 1) _
.PasteSpecial xlPasteValues
End If
Next myCell
Else
myRng.Copy
jLastrow = Sheets("Alldata").Cells(Rows.Count, 1) _
.End(xlUp).Row
myCell.Copy
Sheets("Alldata").Cells(jLastrow + 1, 1) _
.PasteSpecial xlPasteValues
End If
Next

Sheets("Alldata").Rows("1:1").EntireRow.Delete

ws.Activate
End Sub


Gord

On Wed, 17 Jan 2007 14:56:01 -0800, Alina
wrote:

It worked! Thank you :) Only thing I did different was delete the 'orxlToLeft
since I only wanted to shift up. Thank you for the link too, will definitely
use it. Would you also happen to know how to stack or list data from multiple
columns into one column - when the number of rows is undetermined? Sorry to
ask so much...been suffering for 3 days over this.

"Gord Dibben" wrote:

Those cells are not blank if they contain a formula.

You can get rid of these formulas and make the cells truly blank then select
them and delete.

Sub Clear_Stuff()
Dim rng As Range
With Selection
For Each rng In Selection
If rng.HasFormula And rng.Value = "" Then
rng.ClearContents
End If
Next rng
.SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp 'or xlToLeft
End With
End Sub

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + r to open Project Explorer.

Find your workbook/project and select it.

Right-click and InsertModule. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run the macro by going to ToolMacroMacros.

You can also assign this macro to a button or a shortcut key combo.


Gord



On Wed, 17 Jan 2007 09:07:02 -0800, Alina
wrote:

Thanks so much - but for some reason, it's not recognizing that there are any
blanks. My dataset is a copy/paste value from another tab that contains
formulas. The formulas say ="" if criteria is not met. Those end up being my
blank cells. I've tried changing the format. Any help would be appreciated.
Thanks

"Gord Dibben" wrote:

Select the range and F5SpecialBlanksOK

EditDeleteShift Up or Left whichever you choose.

Probably up, looking at your example, but xxxxx doesn't give a good read on
values.

Might be an idea to do this on a copy of the worksheet.


Gord Dibben MS Excel MVP

On Tue, 16 Jan 2007 15:29:01 -0800, Alina
wrote:

My dataset is from A2:G56. Each column has a different header. I have
sporadic blank cells throughout the dataset and want to delete them. How do I
delete the blank cells? See sample below: Thanks in advance :)

Principle Interest Fee1 Fee2 Disbursement
xxxx xxxxx xxxxx
xxxxx xxxxxx
xxxx

delete blanks - sporadic
 

Should read "not" part of the code.

Gord

On Wed, 17 Jan 2007 16:19:16 -0800, Gord Dibben <gorddibbATshawDOTca wrote:

The ' lets Excel know it is part of the code.

delete blanks - sporadic
 

This whole thread "blows-my-mind" the fact that
Alina wants to DELETE the blank cells which are
Nulls as far as I see it.

What am I missing here?



"Gord Dibben" <gorddibbATshawDOTca wrote in message
:

Should read "not" part of the code.

Gord

On Wed, 17 Jan 2007 16:19:16 -0800, Gord Dibben <gorddibbATshawDOTca wrote:

The ' lets Excel know it is part of the code.

delete blanks - sporadic
 

Thanks so much Gord. I tried it but got an error message, so I tried this one
from Toppers on 9/6/06 instead and it works...Thanks again :)

Sub CopyAndMerge()

Dim inrange As Range, outrange As Range
Dim cValue As Variant
Dim r As Long, c As Integer


Set inrange = Application.InputBox(prompt:="Select Input range", Type:=8)
Set outrange = Application.InputBox(prompt:="Select Output range", Type:=8)


Do While Application.Or(outrange.Rows.Count < 1, outrange.Columns.Count < 1)
MsgBox "Output range must be a single cell"
Set outrange = Application.InputBox(prompt:="Select Output range", Type:=8)
Loop


For r = 1 To inrange.Rows.Count

For c = 1 To inrange.Columns.Count
outrange.Value = inrange(r, c).Value
Set outrange = outrange.Offset(1, 0)
Next c

Next r
End Sub

"Gord Dibben" wrote:

Should read "not" part of the code.

Gord

On Wed, 17 Jan 2007 16:19:16 -0800, Gord Dibben <gorddibbATshawDOTca wrote:

The ' lets Excel know it is part of the code.

delete blanks - sporadic
 

They are not "nulls".

They are the "" results of formulas that returned no data so she wants them
shifted up for alignment purposes.


Gord

On Thu, 18 Jan 2007 00:49:41 +0000, "JMay" wrote:

This whole thread "blows-my-mind" the fact that
Alina wants to DELETE the blank cells which are
Nulls as far as I see it.

What am I missing here?



"Gord Dibben" <gorddibbATshawDOTca wrote in message
:

Should read "not" part of the code.

Gord

On Wed, 17 Jan 2007 16:19:16 -0800, Gord Dibben <gorddibbATshawDOTca wrote:

The ' lets Excel know it is part of the code.

delete blanks - sporadic
 

Gord,

It's just that the OP's dataset looks exactly like a table, say

FName LName City
Jim May
Bill Atlanta
Gord Didden New York

OP said:
I have
sporadic blank cells throughout the dataset and want to delete them

So how is the above dataset going to LOOK after
The Blanks are deleted? <<that's my Q

Thanks,



"Gord Dibben" <gorddibbATshawDOTca wrote in message
:

They are not "nulls".

They are the "" results of formulas that returned no data so she wants them
shifted up for alignment purposes.


Gord

On Thu, 18 Jan 2007 00:49:41 +0000, "JMay" wrote:

This whole thread "blows-my-mind" the fact that
Alina wants to DELETE the blank cells which are
Nulls as far as I see it.

What am I missing here?



"Gord Dibben" <gorddibbATshawDOTca wrote in message
:

Should read "not" part of the code.

Gord

On Wed, 17 Jan 2007 16:19:16 -0800, Gord Dibben <gorddibbATshawDOTca wrote:

The ' lets Excel know it is part of the code.

delete blanks - sporadic
 

I guess OP would be the one to answer that.

Aline's response was "It worked! Thank you :) "


Gord

On Thu, 18 Jan 2007 13:38:23 +0000, "JMay" wrote:

Gord,

It's just that the OP's dataset looks exactly like a table, say

FName LName City
Jim May
Bill Atlanta
Gord Didden New York

OP said:
I have
sporadic blank cells throughout the dataset and want to delete them

So how is the above dataset going to LOOK after
The Blanks are deleted? <<that's my Q

Thanks,



"Gord Dibben" <gorddibbATshawDOTca wrote in message
:

They are not "nulls".

They are the "" results of formulas that returned no data so she wants them
shifted up for alignment purposes.


Gord

On Thu, 18 Jan 2007 00:49:41 +0000, "JMay" wrote:

This whole thread "blows-my-mind" the fact that
Alina wants to DELETE the blank cells which are
Nulls as far as I see it.

What am I missing here?



"Gord Dibben" <gorddibbATshawDOTca wrote in message
:

Should read "not" part of the code.

Gord

On Wed, 17 Jan 2007 16:19:16 -0800, Gord Dibben <gorddibbATshawDOTca wrote:

The ' lets Excel know it is part of the code.

delete blanks - sporadic
 

Gord - Thanks for responding.. and I agree
With you ..

"It worked! Thank you :) "

Whatever floats your boat, ehh..



"Gord Dibben" <gorddibbATshawDOTca wrote in message
:

I guess OP would be the one to answer that.

Aline's response was "It worked! Thank you :) "


Gord

On Thu, 18 Jan 2007 13:38:23 +0000, "JMay" wrote:

Gord,

It's just that the OP's dataset looks exactly like a table, say

FName LName City
Jim May
Bill Atlanta
Gord Didden New York

OP said:
I have
sporadic blank cells throughout the dataset and want to delete them

So how is the above dataset going to LOOK after
The Blanks are deleted? <<that's my Q

Thanks,



"Gord Dibben" <gorddibbATshawDOTca wrote in message
:

They are not "nulls".

They are the "" results of formulas that returned no data so she wants them
shifted up for alignment purposes.


Gord

On Thu, 18 Jan 2007 00:49:41 +0000, "JMay" wrote:

This whole thread "blows-my-mind" the fact that
Alina wants to DELETE the blank cells which are
Nulls as far as I see it.

What am I missing here?



"Gord Dibben" <gorddibbATshawDOTca wrote in message
:

Should read "not" part of the code.

Gord

On Wed, 17 Jan 2007 16:19:16 -0800, Gord Dibben <gorddibbATshawDOTca wrote:

The ' lets Excel know it is part of the code.