Hello again.
This looks promissing, however I get an error message that reads something
like:
Run-time error '1004':
The Delete-method in the Range-class was unsuccessful
(translated from Norwegian)
I pasted in the code "as is", only inserted the range "B1:DA519".
Any easy modification? Or is there a way to record the sorting for one row,
and ad a code to the recorded macro to make it repeat it self for a number of
times, always shifting to on row further down the worksheet?
I created the following code:
Sub Sort()
Rows("93:93").Select
Range("B93").Activate
Selection.Sort Key1:=Range("A93"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _
DataOption1:=xlSortNormal
Rows("94:94").Select
Range("B94").Activate
End Sub
I hid column A, so it is not included in the sorting. I started the record
tool, and started by highligthing a rom (no. 93), made the sort, and moved
down to row 94.
Could this be a way??
Regards
Petter Q.
"Tom Ogilvy" wrote:
Sub RemoveBlanks()
Range("B:AZ").SpecialCells(xlBlanks).Delete Shift:=xlShiftLeft
End Sub
would be another guess.
Test it on a copy of your data.
--
Regards,
Tom Ogilvy
"Petterq" wrote in message
...
Hi.
This is something else. On this link i find tips to fill inn blanks, but
that is not the problem.
I need to arrange all celles that have a value from column A and onwards
through B, C and so on. As many columns needed to room all the values. The
number of columns needed will vary from customer to customer.
Example:
CustomerID 8000 in A1
Ordered items: B1:43, D1:55, H1:63
After arranging:
CustomerID 8000 in A1
Ordered items: B1:43, C1:55, D1:63
For on row only it can be done with a sort function, but then I have to
sort
one row at a time. I will have spreadsheets with hundreds of rows, and
then
it will be to time consuming to take it row by row.
Any suggestions to automate the procedure?
"Tom Ogilvy" wrote:
Try looking here for an initial solution:
http://www.contextures.com/xlDataEntry02.html
Debra Dalgleish's site
--
Regards,
Tom Ogilvy
"Petterq" wrote in message
...
I have an Excel spreadsheet that is imported from a Online
orderingsystem,
thus the format is predefined. I need to arrange the data properly in
order
to import theese data to Access. I use Office 2003.
Each line starts with a customerID number in column A. I the first row
is
listed the different items that has been ordered. One item in each
coumn.
If the customer in line 4 has ordered the item listed in column C, the
cell
C4 will have the itemID of that item.
The result is a spreadsheet where each customer has severeal items
ordered,
but each for each item that is not ordered, the corresponding cell is
left
blank.
I need to arrange the data so that each line starts with the
customerID
(as
now), and all the items is listed from column B onwards removing all
the
blanks, and disregarding the item names listed in row 1. Row 1 will be
deleted before the import.
Suggestions?