Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > microsoft.public.excel.programming > #110351

Re: Copying and sorting with some twists

Show all headers | View raw

Hi,

Am Sun, 12 Nov 2017 11:46:48 -0800 (PST) schrieb menken.john@gmail.com:

> A24:A53 ==> copies and pastes it to the "Position Control" spreadsheet into A9:A38
> C24:C53 ==> copies and pastes it to the "Position Control" spreadsheet into C9:C38
> F24:F53 ==> copies and pastes it to the "Position Control" spreadsheet into E9:E38
> H24:H53 ==> copies and pastes it to the "Position Control" spreadsheet into G9:G38
> K24:K53 ==> copies and pastes it to the "Position Control" spreadsheet into I9:I38
> M24:M53 ==> copies and pastes it to the "Position Control" spreadsheet into K9:K38
> P24:P53 ==> copies and pastes it to the "Position Control" spreadsheet into M9:M38
> R24:R53 ==> copies and pastes it to the "Position Control" spreadsheet into O9:O38
> U24:U53 ==> copies and pastes it to the "Position Control" spreadsheet into Q9:Q38
> W24:W53 ==> copies and pastes it to the "Position Control" spreadsheet into S9:S38
> 
> I would then like the names in each of the "Position Control" ranges to be sorted alphabetically. Unfortunately, the users entering names on the "Input" spreadsheet have been known to delete names by pressing the delete key or simply entering a blank over the name. The Lastname, Firstname's may not be contiguous. The sort should take this into account and still show names at the top of the sorted column in the "Position Control" spreadsheet.

try:

Sub Test()
Dim rng1 As Range, rng2 As Range
Dim ar As Range, rngC As Range
Dim i As Integer

Set rng1 = Sheets("Input").Range("A24:A53,C24:C53,F24:F53," _
   & "H24:H53,K24:K53,M24:M53,P24:P53,R24:R53,U24:U53,W24:W53")
   
Set rng2 = Sheets("Position Control").Range("A9:A38,C9:C38,E9:E38," _
   & "G9:G38,I9:I38,K9:K38,M9:M38,O9:O39,Q9:Q38,S9:S38")
   
For i = 1 To rng1.Areas.Count
   With rng2.Areas(i)
      .Value = rng1.Areas(i).Value
      .Sort key1:=.Cells(1), order1:=xlAscending, Header:=xlNo
   End With
Next
End Sub


Regards
Claus B.
-- 
Windows10
Office 2016

Back to microsoft.public.excel.programming | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

Copying and sorting with some twists menken.john@gmail.com - 2017-11-12 11:46 -0800
  Re: Copying and sorting with some twists Claus Busch <claus_busch@t-online.de> - 2017-11-13 15:14 +0100
    Re: Copying and sorting with some twists menken.john@gmail.com - 2017-11-13 11:49 -0800
    Re: Copying and sorting with some twists GS <gs@v.invalid> - 2017-11-13 16:14 -0500

csiph-web