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