[RESOLVED] Copy cell range until cell contains zero

Hi:

I need a macro to copy a range of cells starting in FT2:FX2
Checkin in col FT for a blank cell or Col FU for a zero (0) signaling the end of the data range.

I then need to select this range of cells and sort them by date col FT in descending order (Z to A).

Last, I need to copy these cells into a new range starting in cell FY2.

I know how to do all of this except check for the end of data flag?

Thanks for any suggestions / assistance,

Michael

to return the last column index with data, try like

Code:

---

for each cel in range("ft2:fx2")
  if isempty(cel) or cel = 0 then lastcol = cel.column - 1: exit for
next

---

you can then use the last column index for your range

Hello Pete:

Thanks for your assistance. I understand your code, but not exactly sure how to integrate it within my macro?

Code:

---

Sub ZigZagReSort2End()
'
' ZigZagReSort2End Macro

    For Each cel In Range("ft2:fx2")
  If IsEmpty(cel) Or cel = 0 Then lastcol = cel.Column - 1: Exit For
Next
Range("FT2:FX201").Select
    ActiveWorkbook.Worksheets("F").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("F").Sort.SortFields.Add Key:=Range("FT3:FT201"), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("F").Sort
      .SetRange Range("FT2:FX201")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
 
End Sub

---

Is there a way to replace the . Range("FT2:FX201"), to the new range, defined by your code?
There may be a much simpler way that I am not aware of just as I expected a nested IF statement in order to be able to detect the last cell?

I also just noticed that the active worksheet is being addressed by the tab name which is also the current stock ticker being analyzed. What generic name do I replace that ("F") with so that when I change the ticker symbol in the tab, all of my macros still work?

Thanks Michael

Michael,

Does your data start in FT2 and go across to FX2, and down for a variable number of rows? So, for example, the data continues downward until row 99 where we find either a blank in FT99 or a zero in FU99?

Quote:

---

Originally Posted by vbfbryce

Michael,

Does your data start in FT2 and go across to FX2, and down for a variable number of rows? So, for example, the data continues downward until row 99 where we find either a blank in FT99 or a zero in FU99?

---

Yes, actually after it is sorted the first time it is possible for there to be as many as 200 rows of data. The Col range is indeed FT through FX.

I just need to "Select" all the rows up to, but not including the first blank or zero so that the next sort rang is defined. This will segregate only my target data for the next step.

Thanks, Michael

Ok, so maybe something like this to find the last row of the data (assumes that the BLANK in FT and the ZERO in FU don't necessarily happen in the same row, but that could be wrong):

Code:

---

Sub findBottom()
    Dim ws As Worksheet
    Dim firstBlankRow As Long
    Dim firstZeroRow As Long
    Dim lastRow As Long
    Dim i As Long
   
    Set ws = ActiveSheet
    firstBlankRow = ws.Range("ft2").End(xlDown).Row + 1
    If firstBlankRow - 1 = ws.Rows.Count Then
        firstBlankRow = 1
        Exit Sub
    End If
    For i = 2 To firstBlankRow - 1
        If ws.Range("fu" & i).Value = 0 Then
            firstZeroRow = i
            Exit For
        End If
    Next i
    If Application.WorksheetFunction.Min(firstBlankRow, firstZeroRow) = 0 Then
        lastRow = Application.WorksheetFunction.Max(firstBlankRow, firstZeroRow) - 1
    Else
        lastRow = Application.WorksheetFunction.Min(firstBlankRow, firstZeroRow) - 1
    End If
End Sub

---

Thanks FM:

How do I extract the sort range from you Sub?

Is the answer to my sectioned question "ActiveSheet"? to produce a generic reference that will ignore the actual name of the sheet?

Thanks, Michael

You incorporate "lastRow" into the code. Tack something like this onto the end of the previous code (before the End Sub):

Code:

---

Dim rngKey As Range
    Dim rngSort As Range
   
    Set rngSort = ws.Range("FT1:FX" & lastRow)
    Set rngKey = ws.Range("FT2:FT" & lastRow)
   
    With ws.Sort
        .SortFields Clear
        .SortFields.Add Key:=rngKey, SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
        .SetRange rngSort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

---

Thanks Vbfbryce:

Below is the combined code. On first run I got the following error code: Compile Error: Invalid Use of Property

With the following row/text highlighted: "SortFields" and then unhighlighted the word "Clear."

I though that this indicated "SortFields" needed to be defined with DIM so I tried that. Still not working.

Thanks, Michael

Code:

---

Sub findBottomWithSort2()
    Dim ws As Worksheet
    Dim firstBlankRow As Long
    Dim firstZeroRow As Long
    Dim lastRow As Long
    Dim i As Long
    Dim SortFields As Areas
    Set ws = ActiveSheet
    firstBlankRow = ws.Range("ft2").End(xlDown).Row + 1
    If firstBlankRow - 1 = ws.Rows.Count Then
        firstBlankRow = 1
        Exit Sub
    End If
    For i = 2 To firstBlankRow - 1
        If ws.Range("fu" & i).Value = 0 Then
            firstZeroRow = i
            Exit For
        End If
    Next i
    If Application.WorksheetFunction.Min(firstBlankRow, firstZeroRow) = 0 Then
        lastRow = Application.WorksheetFunction.Max(firstBlankRow, firstZeroRow) - 1
    Else
        lastRow = Application.WorksheetFunction.Min(firstBlankRow, firstZeroRow) - 1
    End If
    Dim rngKey As Range
    Dim rngSort As Range
   
    Set rngSort = ws.Range("FT1:FX" & lastRow)
    Set rngKey = ws.Range("FT2:FT" & lastRow)
   
    With ws.Sort
      .SortFields Clear
        .SortFields.Add Key:=rngKey, SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
        .SetRange rngSort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

---

sorry, was supposed to have been ".SortFields.Clear" with the period, not a space in there.

That worked like a dream, thanks to all for you very professional assistance,

Michael