Tricky Dynamic Range

[johngio]

Hi all,

I'm trying to set up a dynamic range - got the columns sussed, but the rows are a bit trickeir. Here is my scenario.

I have a range of values in the Y and Z column. These can appear anywhere in the column.

I have values in the G column. These can start anywhere from Row 16 to Row 39.

I want to set up my dynamic array FROM:

The FIRST non zero entry in the G column AFTER row 15.

TO:

The last non zero entry in EITHER row Y or Z.


I know for the TO part I can do:

Cells(Rows.Count, "Z").End(xlUp).Row

Well, that covers the Z column anyways - however it doesn't restrict itself to non zeros. . .

Any ideas??

Thanks

John

 

[acw]

John

This is extremely clunky but should get you the range.

Sub aaa()
  Dim GRow As Integer
  Dim LastCol As Integer, LastRow As Integer
  'G row
  i = 15
  Do
    i = i + 1
  Loop Until Cells(i, 7) > 0
  GRow = Cells(i, "G").Row
  
  Roww = Cells(Rows.Count, 25).End(xlUp).Row + 1
  Do
    Roww = Roww - 1
  Loop Until Cells(Roww, 25) > 0
  LastRow = Roww
  LastCol = 25
  
  Roww = Cells(Rows.Count, 26).End(xlUp).Row + 1
  Do
    Roww = Roww - 1
  Loop Until Cells(Roww, 26) > 0
  
  If Roww > LastRow Then
    LastRow = Roww
    LastCol = 26
  End If
  
  Range(Cells(GRow, "G"), Cells(LastRow, LastCol)).Select
  MsgBox GRow
End Sub

Tony

 

[johngio]

Thanks Tony.

This formula gives me the number of cells in the range, but I can muck around with it and get the first and last cells.

Only 1 thing (which I am currently trying to work on.

Basically I want to start my macro with:

For iRow = something to something
For iCol = 7 to 9

and specify the range here.

Any idea how I can adapt this formula to fit this format

 

[acw]

John

This is a bit more compact

Sub bbb()
  Dim LastRow As Integer, LastCol As Integer, GRow As Integer
  
  'G row
  i = 15
  Do
    i = i + 1
  Loop Until Cells(i, 7) > 0
  GRow = Cells(i, "G").Row
  
  'Y or Z
  Set findit = Range("Y:Z").Find(what:="*", searchDirection:=xlPrevious, after:=Range("Z1"))
  roww = findit.Row + 1
  Do
  roww = roww - 1
  Loop Until Cells(roww, "Y") > 0 Or Cells(roww, "Z") > 0
  
  LastRow = roww
  If Cells(LastRow, "Y") > 0 Then
    LastCol = 25
  Else
    LastCol = 26
  End If
  Range(Cells(GRow, "G"), Cells(LastRow, LastCol)).Select
  
End Sub

Both bits of code give you the relevant start and end cells.

Cells(GRow,"G") gives you the first cell in column G
cells(lastrow,lastcol) will give you the last cell in either Y or Z that is > 0

I only put in the range selection statement to show how the range could be selected.

Tony

 

[johngio]

That is awesome!

Thanks AGAIN Tony