Sorting columns.

[Art MacNeil]

Hi all,

I have a spreadsheet with about 300 tabs. Some have data in Column A
only, some have data in Column A through Column IV. I want to have a Macro
that will go through each tab and move everything into Column A on that tab.
So, any data in Column B gets moved underneath the data in Column A. Then
any data in Column C will be moved underneath the data from Column A and
Column B, in Column A, etc.

Then, I'd like the macro to delete any blank lines on each tab. (There will
be lots of them).

I've tried to do this but my way has created a macro that is now a over a
thousand lines long. I'm hoping there is a small, smart way to do this.

Any ideas?

Thanks,

Art.

 

[Dave Peterson]

How about:

Option Explicit
Sub testme()
Dim wks As Worksheet
Dim iCol As Long
Dim LastCol As Long

With ActiveWorkbook
For Each wks In .Worksheets
With wks
With .UsedRange
LastCol = .Columns(.Columns.Count).Column
End With
For iCol = 2 To LastCol
On Error Resume Next
.Range(.Cells(1, iCol), _
.Cells(.Rows.Count, iCol).End(xlUp)).Copy _
Destination:=.Cells(.Rows.Count, "A").End(xlUp) _
.Offset(1, 0)
If Err.Number <> 0 Then
MsgBox "Something went wrong with column: " & iCol _
& vbLf & "on sheet: " & .Name
Err.Clear
Exit Sub
End If
On Error GoTo 0
Next iCol
.Range("b1", .Cells(1, .Columns.Count)).EntireColumn.Delete
On Error Resume Next
.Range("a:a").Cells.SpecialCells(xlCellTypeBlanks) _
.EntireRow.Delete
On Error GoTo 0
End With
Next wks
End With

End Sub

 

[Art MacNeil]

That worked perfectly

Thank you very much,

Art.

 

[Art MacNeil]

Odd side effect of running the Macro.

My spreadsheet almost doubled in size, it was originally 7 Megs and is now
13 Megs.

No matter though, I'd take it even if it was 40 Megs.

Thanks again.

Art.

That worked perfectly

Thank you very much,

Art.

 

[Dave Peterson]

You may want to try resetting the last used cell of each sheet.

This minor modification may do it:

Option Explicit
Sub testme()
Dim wks As Worksheet
Dim iCol As Long
Dim LastCol As Long
Dim DummyRng as Range '<-- Added

With ActiveWorkbook
For Each wks In .Worksheets
With wks
With .UsedRange
LastCol = .Columns(.Columns.Count).Column
End With
For iCol = 2 To LastCol
On Error Resume Next
.Range(.Cells(1, iCol), _
.Cells(.Rows.Count, iCol).End(xlUp)).Copy _
Destination:=.Cells(.Rows.Count, "A").End(xlUp) _
.Offset(1, 0)
If Err.Number <> 0 Then
MsgBox "Something went wrong with column: " & iCol _
& vbLf & "on sheet: " & .Name
Err.Clear
Exit Sub
End If
On Error GoTo 0
Next iCol
.Range("b1", .Cells(1, .Columns.Count)).EntireColumn.Delete
On Error Resume Next
.Range("a:a").Cells.SpecialCells(xlCellTypeBlanks) _
.EntireRow.Delete
On Error GoTo 0
Set DummyRng = .UsedRange '<-- Added
End With
Next wks
End With

End Sub

If you don't want to rerun the macro, you could try the techniques at Debra
Dalgleish's site:
http://contextures.com/xlfaqApp.html#Unused

Odd side effect of running the Macro.

My spreadsheet almost doubled in size, it was originally 7 Megs and is now
13 Megs.

No matter though, I'd take it even if it was 40 Megs.

Thanks again.

Art.

 

[Art MacNeil]

I ran the new Macro and the size of the spreadsheet went from 13,067 kb to
12,816 kb.

As I said, I'm not worried at all about the size of the spreadsheet. I just
thought it was odd that it almost doubled.

Thank you for your time and effort,

Art.

 

[Art MacNeil]

Hello Mr. Peterson,

I have posted several questions in the last 2 years to the Excel newsgroup
and you and others have helped me. I was wondering if you could recommend a
book or an online course that I could take to expand my knowledge of Macros.
I usually need to grab data from hundreds of different spreadsheets, or
update those spreadsheets with data from a single spreadsheet.

I've found your macros work very well I'd like to be able to have enough
knowledge to attempt my work with a little more ability. If I run into
problems, then maybe I'd post to the newsgroup to get some clarification.
My goal is to learn to do write these Macros for myself. Once I get more
knowledge, it would be a thrill for me to actually help someone else in the
newsgroup with their questions.

I would appreciate any guidance you could offer.

Yours truly,

Art MacNeil.

I ran the new Macro and the size of the spreadsheet went from 13,067 kb to
12,816 kb.

As I said, I'm not worried at all about the size of the spreadsheet. I
just thought it was odd that it almost doubled.

Thank you for your time and effort,

Art.

 

[Dave Peterson]

Debra Dalgleish has a list of books at her site:
http://www.contextures.com/xlbooks.html

John Walkenbach's is a nice one to start with.

See if you can find them in your local bookstore/internet site and you can
choose what one you like best.

Hello Mr. Peterson,

I have posted several questions in the last 2 years to the Excel newsgroup
and you and others have helped me. I was wondering if you could recommend a
book or an online course that I could take to expand my knowledge of Macros.
I usually need to grab data from hundreds of different spreadsheets, or
update those spreadsheets with data from a single spreadsheet.

I've found your macros work very well I'd like to be able to have enough
knowledge to attempt my work with a little more ability. If I run into
problems, then maybe I'd post to the newsgroup to get some clarification.
My goal is to learn to do write these Macros for myself. Once I get more
knowledge, it would be a thrill for me to actually help someone else in the
newsgroup with their questions.

I would appreciate any guidance you could offer.

Yours truly,

Art MacNeil.

 

[Art MacNeil]

That works for me.

Thanks,

Art.

Debra Dalgleish has a list of books at her site:
http://www.contextures.com/xlbooks.html

John Walkenbach's is a nice one to start with.

See if you can find them in your local bookstore/internet site and you can
choose what one you like best.