Select a range (column) of cells with variable length

[valentina_putrino]

Hi all
I am trying to write a macro that call each cell content of a column in sheet 'A' and past it in sheet 'B' to process some calcs and then to copy the results in sheet C.
Below my code. What I would like is to find a way to avoid inputting the last cell of my range (here equal to 31) and to leave the end of my variable 'i' to select any number in the column until it finds 0. Can you help?

Sub mech_A0()

Sheets("A").Select
For i = 3 To 31
Sheets("A").Select
cc = Cells(i, 34)
Sheets("B").Select
Cells(23, 2) = cc
Sheets("B").Select
m = Cells(29, 18)
cont = cont + 1
Sheets("C").Select
Cells(2 + cont, 1) = m
Next i
End Sub


Many thanks in advance
Valentina

 

[kweaver]

Re: How to select a range (column) of cells with variable lenght

You can calculate the last row with something like this:

LR = Sheets("A").Cells(Rows.Count,"A").End(xlUp).Row

Then, use that in your routine:

For i = 3 To LR

 

[kweaver]

Re: How to select a range (column) of cells with variable lenght

I reread your post and see you want the last row to be the row of the last value if a 0 is encountered.

I think this does it:

Sub last0()
For i = 1 To Rows.Count
 If 0 = Sheets("Sheet2").Cells(i, "A").Value Then
 lastrow = i - 1
 MsgBox lastrow
 Exit Sub
 Else
 End If
Next i
End Sub

 

[valentina_putrino]

Re: How to select a range (column) of cells with variable lenght

Hi - thanks in advance
I rewrote my code like this

Sub mech_A0()
'mechanism A0'
Sheets("A").Select
cc = Cells(i, 34) 'the columns I want to copy is the number 34'
For i = 3 To Rows.Count

'the first number of the column I want to copy is at row no.3'​

If 0 = Sheets("A").Cells(i, "A").Value Then
Lastrow = i - 1
MsgBox Lastrow
Exit Sub
Else
End If
Sheets("B").Select
Cells(23, 2) = cc
Sheets("B").Select
m = Cells(29, 18)
cont = cont + 1
Sheets("C").Select
Cells(2 + cont, 1) = m
Next i
End Sub



Still it doesn't work.
Any additional suggestion? I kindly appreciate your help.
Valentina

 

[kweaver]

Re: How to select a range (column) of cells with variable lenght

Good Morning (at least it is in California). See if this is closer or exactly what you want:

Sub mech_A0()
'mechanism A0'
Dim cont As Long
Dim F As Long  ' first 0 in column A

cont = 0

Sheets("A").Select
' Set F = Range("A1:A" & Rows.Count).Find(What:=0, LookAt:=xlWhole, SearchDirection:=xNext)
F = 1
Do Until Range("A" & F) = 0
F = F + 1
Loop
F = F - 1  ' this is the row number of the cell before the first 0

For i = 3 To F
'the first number of the column I want to copy is at row no.3
'cc = Sheets("A").Cells(i, 34) 'the columns I want to copy is the number 34
Sheets("B").Cells(23, 2) = Sheets("A").Cells(i, 34) 'the columns I want to copy is the number 34
cont = cont + 1
Sheets("C").Cells(2 + cont, 1) = Sheets("B").Cells(29, 18)
Next i
End Sub

Kevin

 

[valentina_putrino]

Re: How to select a range (column) of cells with variable lenght

Hi Kevin
thanks but it doesn't work.
Do you have any other suggestion ?
I add below my original code - without changing the names of the sheets and I will comment in arial bold on a side what it is and what I want to be changed. This might be more effective.
thanks

Sub mech_A0()
'mechanism A0'
Sheets("water").Select
For i = 3 To 31 ' the column that I want to pick the value from is AH which correspond to column 34 mentioned below. For this I want an open ended loop that select instead of the values 3 to 31, from 3 to the end of the column until the first cell that is empty'
Sheets("water").Select
A_VALUE = Cells(i, 34)
Sheets("Walls_FREE_EDGE").Select
Cells(23, 2) = A_VALUE
Sheets("Walls_FREE_EDGE").Select
m = Cells(29, 25)
q_1 = Cells(25, 19) 'a<KH'
a_1 = Cells(25, 20) 'a<KH'
den1_1 = Cells(27, 20) 'a<KH'
den1_2 = Cells(27, 21) 'a<KH'
m_1 = Cells(29, 20) 'a<KH'

q_2 = Cells(25, 22) 'a>KH'
a_2 = Cells(25, 23) 'a>KH'
b_2 = Cells(25, 24) 'a>KH'
den2_1 = Cells(27, 23) 'a>KH'
den2_2 = Cells(27, 24) 'a>KH'
m_2 = Cells(29, 23) 'a>KH'
cont = cont + 1
Sheets("Wall_FREE_EDGE_R").Select
Cells(2 + cont, 1) = q_1
Cells(2 + cont, 2) = a_1
Cells(2 + cont, 3) = den1_1
Cells(2 + cont, 4) = den1_2
Cells(2 + cont, 5) = m_1
Cells(2 + cont, 6) = q_2
Cells(2 + cont, 7) = a_2
Cells(2 + cont, 8) = b_2
Cells(2 + cont, 9) = den2_1
Cells(2 + cont, 10) = den2_2
Cells(2 + cont, 11) = m_2
Cells(2 + cont, 13) = m
Cells(2 + cont, 14) = A_VALUE
Next i
End Sub


thanks
valentina