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

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

Re: Rearrange numbers in a range

Show all headers | View raw

Hi Derick,

Am Fri, 23 Oct 2015 08:20:48 +0200 schrieb Claus Busch:

> try:
> 
> Sub Rearrange()

sorry, forgot the case if all numbers greater 10

Try:

Sub Rearrange()
Dim myCnt As Long, i As Long, n As Long
Dim varData() As Variant

myCnt = Application.CountIf(Range("B4:G4"), "<11")
If myCnt = 0 Then Exit Sub

Application.ScreenUpdating = False

If myCnt = 6 Then
    For i = 5 To 5 - 2 + myCnt
        Range(Cells(i, 2), Cells(i, 6)).Value _
            = Range(Cells(i - 1, 3), Cells(i - 1, 7)).Value
        Cells(i, 7) = Cells(i - 1, 2)
    Next
Else
    For i = 2 To 7
        ReDim Preserve varData(myCnt - 1)
        If Cells(4, i) < 11 Then
            varData(n) = Cells(4, i)
            n = n + 1
        End If
    Next
    Range("B5").Resize(, UBound(varData) + 1) = varData
    For i = 6 To 6 - 1 + UBound(varData)
        Range(Cells(i, 2), Cells(i, 2 + UBound(varData))).Value _
            = Range(Cells(i - 1, 3), Cells(i - 1, 2 +
UBound(varData))).Value
        Cells(i, 2 + UBound(varData)) = Cells(i - 1, 2)
    Next
End If
Application.ScreenUpdating = True
End Sub


Regards
Claus B.
-- 
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

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

Thread

Rearrange numbers in a range derick.gasimperial@gmail.com - 2015-10-22 21:18 -0700
  Re: Rearrange numbers in a range Claus Busch <claus_busch@t-online.de> - 2015-10-23 08:20 +0200
    Re: Rearrange numbers in a range Claus Busch <claus_busch@t-online.de> - 2015-10-23 08:40 +0200
      Re: Rearrange numbers in a range derick.gasimperial@gmail.com - 2015-10-23 21:10 -0700
        Re: Rearrange numbers in a range Claus Busch <claus_busch@t-online.de> - 2015-10-24 08:20 +0200
          Re: Rearrange numbers in a range derick.gasimperial@gmail.com - 2015-10-25 19:07 -0700
  Re: Rearrange numbers in a range GS <gs@v.invalid> - 2015-10-24 22:29 -0400
  Re: Rearrange numbers in a range Bruno Campanini <brunocam@libero.it> - 2015-10-26 01:32 +0100
    Re: Rearrange numbers in a range derick.gasimperial@gmail.com - 2015-10-25 19:10 -0700

csiph-web