UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Vba/macro Code To Loop Through Rows, Sort Left To Right, Asc Order Excel, Office 2013    
 
   
freespiritcheris...
post Aug 14 2019, 06:10 AM
Post#1



Posts: 152
Joined: 19-October 04



Dear UA friends,

Im looking for a macro that will perform this function. I had already asked before and got a lovely piece of code to copy and paste and shared it here, but its gone! have searched high and low in here and search online but nothing seemed as simple. Any assistance would be appreciated.

regards
freesp


Attached File(s)
Attached File  Capture.PNG ( 32.25K )Number of downloads: 6
 
Go to the top of the page
 
RJD
post Aug 14 2019, 07:02 AM
Post#2


UtterAccess VIP
Posts: 10,091
Joined: 25-October 10
From: Gulf South USA


Hi: See if this macro does what you want...

Sub SortHorizontally()
Dim i As Integer
For i = 1 To 7
Range("a" & i & ":f" & i).Sort Key1:=Range("a" & i & ":f" & i), Order1:=xlAscending, Orientation:=xlLeftToRight
Next i
End Sub

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
freespiritcheris...
post Aug 14 2019, 08:09 AM
Post#3



Posts: 152
Joined: 19-October 04



ooohhhh...my heart!!!

of course it worked!!!! Thank you so much RJD, I spent hours searching! thanks.gif notworthy.gif
Go to the top of the page
 
RJD
post Aug 14 2019, 09:53 AM
Post#4


UtterAccess VIP
Posts: 10,091
Joined: 25-October 10
From: Gulf South USA


You are very welcome.

Regards,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
ADezii
post Aug 14 2019, 09:58 AM
Post#5



Posts: 2,679
Joined: 4-February 07
From: USA, Florida, Delray Beach


@RJD:
If you have a moment, can you kindly tell me where I went wrong in attempting to solve this issue? pullhair.gif Your expertise is truly appreciated, thanks.
This post has been edited by ADezii: Aug 14 2019, 09:58 AM
Attached File(s)
Attached File  Free_Spirit.zip ( 22.64K )Number of downloads: 2
 
Go to the top of the page
 
RJD
post Aug 14 2019, 10:48 AM
Post#6


UtterAccess VIP
Posts: 10,091
Joined: 25-October 10
From: Gulf South USA


Hi: Well, it looks like your solution works perfectly. It is the same basic approach as mine, just going about it with more code and with variable rows (more generalized than mine).

Actually, my expertise in this area is a bit thin. I had to go look up the sort syntax and build from there ...

Regards,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
ADezii
post Aug 14 2019, 11:16 AM
Post#7



Posts: 2,679
Joined: 4-February 07
From: USA, Florida, Delray Beach


  1. Now I really know that I am going crazy.
  2. If you compare the Original Data to the expected Results, Range "B4:D4" does not appear to be correct.

This post has been edited by ADezii: Aug 14 2019, 11:17 AM
Attached File(s)
Attached File  R1.JPG ( 31.45K )Number of downloads: 0
Attached File  R2.jpg ( 31.27K )Number of downloads: 0
 
Go to the top of the page
 
RJD
post Aug 14 2019, 11:21 AM
Post#8


UtterAccess VIP
Posts: 10,091
Joined: 25-October 10
From: Gulf South USA


Well, it looks like the original row 4 has two 1s and no 4, thus the different looking row after sort. But the sort looks correct. Am I seeing this correctly?

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
ADezii
post Aug 14 2019, 12:00 PM
Post#9



Posts: 2,679
Joined: 4-February 07
From: USA, Florida, Delray Beach


QUOTE
it looks like the original row 4 has two 1s and no 4

That's correct, I was just going by the expected results as posted by freespirit, namely:

Attached File(s)
Attached File  Results.JPG ( 17.86K )Number of downloads: 0
 
Go to the top of the page
 
freespiritcheris...
post Aug 14 2019, 12:04 PM
Post#10



Posts: 152
Joined: 19-October 04



... oh my goodness, apologies for the error Adezil.


Double vision working graveyard shifts again, sorry.

and, that is a really handy tool! How can I use that where the number of rows increase? I used RJDs which was perfectly needed at an urgent time and I did have to change the row number in the code for each spreadsheet I processed. They all have 7 columns but the rows increase everytime I get a new spreadsheet. I had one spreadsheet with 1170 rows!

as always, much appreciated.
free
This post has been edited by freespiritcherishes: Aug 14 2019, 12:07 PM
Go to the top of the page
 
RJD
post Aug 14 2019, 12:06 PM
Post#11


UtterAccess VIP
Posts: 10,091
Joined: 25-October 10
From: Gulf South USA


Adezii: Right. Looks like the OP typed the cells individually, and miss-typed a cell in the original set ... or that is my assumption ...

Regards,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
freespiritcheris...
post Aug 14 2019, 12:08 PM
Post#12



Posts: 152
Joined: 19-October 04



You assume correctly RJD,
profuse apologies... fundrink.gif .. fatigue is my only excuse. sleeping.gif
Go to the top of the page
 
RJD
post Aug 14 2019, 12:14 PM
Post#13


UtterAccess VIP
Posts: 10,091
Joined: 25-October 10
From: Gulf South USA


thumbup.gif

As I mentioned before, yes, ADezii's solution is generalized, whereas my original approach simply dealt with what you posted. Here's another approach, using a maximum rows approach, with a way out if the Ai cell is null ...

QUOTE
Sub SortHorizontally()
Dim i As Integer
For i = 1 To 3000
If IsNull(Range("A" & i)) Then Exit Sub
Range("a" & i & ":f" & i).Sort Key1:=Range("a" & i & ":f" & i), Order1:=xlAscending, Orientation:=xlLeftToRight
Next i
End Sub


ADezii's solution, of course, looks to the last row and deals with it that way.

Just another thought ...

Regards,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
ADezii
post Aug 14 2019, 12:23 PM
Post#14



Posts: 2,679
Joined: 4-February 07
From: USA, Florida, Delray Beach


QUOTE
and, that is a really handy tool! How can I use that where the number of rows increase?

Download the Attachment in Post# 5 where I asked for RJD's intervention (not alcohol related!). As long as the Upper Left Cell of the Range is $A$1, and the Data is contiguous, the Code should allow for a variable number of Rows. Simply change the following line of Code within the Code Block
CODE
Set sht = ActiveWorkbook.Worksheets("Sheet1")

to
CODE
Set sht = ActiveWorkbook.Worksheets("Name of Worksheet containing Range")

P.S. - As far as the typo goes, no problem, I was more concerned about my sanity. laugh.gif
Go to the top of the page
 
freespiritcheris...
post Aug 14 2019, 12:38 PM
Post#15



Posts: 152
Joined: 19-October 04



corr... nice one! I shall use that, thanks.gif Adezil notworthy.gif

Isn't getting older, cruel.. one has to check ones sanity as forgetfulness creeps up more rapidly.. I'd rather my excuse be that i'd been at the Sherry... again wine.gif but unfortunately.. not!

and thank you for the understanding RJD, you guys are amazing.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    19th October 2019 - 12:14 AM