Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I can't delete an image in excel workbook | Excel Discussion (Misc queries) | |||
I can't delete a cell without deleting a row | Excel Discussion (Misc queries) | |||
how do I delete a worksheet from my workbook | New Users to Excel | |||
Delete row depending on criteria | Excel Discussion (Misc queries) | |||
excel needs feature to delete blanks when autofilter is used. | Excel Discussion (Misc queries) |