Re: Move Non Zero Columns Left?
- From: GM7 <arkins@xxxxxxxxx>
- Date: Fri, 16 Jan 2009 14:20:26 -0800 (PST)
Thanks very much, Rich!
I'll try this first thing Tuesday.
Patrick
On Jan 16, 4:47 pm, Rich P <rpng...@xxxxxxx> wrote:
I think I get it now. To automate this will require a little bit of
coding. First set up your spreadsheet by creating a similar set of data
directly adjacent to your current set: Ex -- in the spreadsheet you
currently have the following columns
A B C D E F G H I J k l M N O
Fred 0 0 0 3 9 7 0 0 5 9 | | | | | | ...
..
Starting at column M create the same borders as you have in the first
set and set the values all to 0 -- use the Accounting format if you want
- dashes in place of 0. Then in the Visual Basic Editor we will add a
subroutine that will copy only numbers in each row which are greater
than 0 to adjacent cells on the same row. In the Tools Menu goto Macro
to Visual Basic Editor. Now insert a code module in the project from
the Insert menu. Copy this routine into the module
Sub CopyNumbers()
Dim rng As Range, i As Integer, j As Integer, k As Integer
set rng = Range("B1:K10, M1:V10)
For i = 1 To rng.Areas(1).Rows.Count
k = 1
For j = 1 To rng.Areas(1).Columns.Count
If rng.Areas(1)(i, j) <> 0 Then
rng.Areas(2)(i, k) = rng.Areas(1)(i, j)
k = k + 1
End If
Next
Next
End Sub
To experiment with this code do this: In a blank sheet in the same
Excel File add some fake data starting at row1 "A1" as above. Add Fred
and some numbers with 0's starting at column B to column K. Then add
Barney on row2, Wilma row3, ... don't even bother with borders- we are
just testing out the routine. Add 10 rows of data because I have set my
range object to read 10 rows of data. Make sure to intersperse some 0's
in your 10 rows. What the routine above will do is to read the 10 rows
and copy only the non zero numbers to a 2nd range which will be adjacent
to the first set starting at column M.
Now go back to the Tools menu in your sheet and goto Macro then select
Macros. You will see the CopyNumbers macro in the list. It should be
highlighted. Click the Run button and watch what happens. All the
numbers which are greater than zero will get copied to the cells
starting at M. This routine can copy 10,000 rows the same way in a
matter of seconds. Way better than doing it by hand.
Rich
*** Sent via Developersdexhttp://www.developersdex.com***
.
- Follow-Ups:
- Re: Move Non Zero Columns Left?
- From: GM7
- Re: Move Non Zero Columns Left?
- References:
- Re: Move Non Zero Columns Left?
- From: GM7
- Re: Move Non Zero Columns Left?
- From: Rich P
- Re: Move Non Zero Columns Left?
- Prev by Date: Re: Move Non Zero Columns Left?
- Next by Date: Re: Access 97 on a Windows 2003 server
- Previous by thread: Re: Move Non Zero Columns Left?
- Next by thread: Re: Move Non Zero Columns Left?
- Index(es):
Relevant Pages
|
