Help stopping when row is blank
 

I am new to VBA. I recorded a macro and applied it to the command
button to fill two seperate ranges down. Now I want to edit the code to
do the following:
After I run (click the command button), the cursor stops at the cell
"F1169". I fill in the cells in column "H" with an "x" to indicate I
have finished analyizing that row. I would like the cursor to change
locations based on if the cell in column H is blank by going to the
last cell in column H with an "x" and then offset (-1,-2).
Also, when I recorded the macro, I set the range (to fill down) to
1500. I add new rows to include more data daily. The 1500# was overkill
and I only used it because I will never have that much data. I would
like to clean up the range down to stop if there is nothing in the last
row of column "C". I hope some of this makes sense. I have been
researching to find an answer on my own with no luck. Please help.

Private Sub CommandButton1_Click()
Range("F4").Select
Selection.AutoFill Destination:=Range("F4:F1500"),
Type:=xlFillDefault
Range("F4:F1500").Select
ActiveWindow.LargeScroll Down:=-42
ActiveWindow.SmallScroll Down:=-21
Range("O4:P4").Select
Selection.AutoFill Destination:=Range("O4:P1500"),
Type:=xlFillDefault
Range("O4:P1500").Select
ActiveWindow.LargeScroll Down:=-19
ActiveWindow.SmallScroll Down:=249
Range("F1169").Select
End Sub

Help stopping when row is blank
 

PJ, the code

Range("H65536").End(xlUp).Offset(-1, -2).Select

will select the cell one row above and two columns to the left of the
last cell in Column H with an "x" in it.

Can you be more specific about what cells you want to do? Say there
are data in cells F1:F25 and "x"s in cells H1:H10. What specific cells
do you want selected and what cells do you want autofilled?


Mark


PJ wrote:
I am new to VBA. I recorded a macro and applied it to the command
button to fill two seperate ranges down. Now I want to edit the code to
do the following:
After I run (click the command button), the cursor stops at the cell
"F1169". I fill in the cells in column "H" with an "x" to indicate I
have finished analyizing that row. I would like the cursor to change
locations based on if the cell in column H is blank by going to the
last cell in column H with an "x" and then offset (-1,-2).
Also, when I recorded the macro, I set the range (to fill down) to
1500. I add new rows to include more data daily. The 1500# was overkill
and I only used it because I will never have that much data. I would
like to clean up the range down to stop if there is nothing in the last
row of column "C". I hope some of this makes sense. I have been
researching to find an answer on my own with no luck. Please help.

Private Sub CommandButton1_Click()
Range("F4").Select
Selection.AutoFill Destination:=Range("F4:F1500"),
Type:=xlFillDefault
Range("F4:F1500").Select
ActiveWindow.LargeScroll Down:=-42
ActiveWindow.SmallScroll Down:=-21
Range("O4:P4").Select
Selection.AutoFill Destination:=Range("O4:P1500"),
Type:=xlFillDefault
Range("O4:P1500").Select
ActiveWindow.LargeScroll Down:=-19
ActiveWindow.SmallScroll Down:=249
Range("F1169").Select
End Sub

Help stopping when row is blank
 

First off....thanks a million. I added the code
"Range("H65536").End(xlUp).Offset(-1, -2).Select" and it did exactly
what I wanted it to do. In Layman's terms could you help me understand
what I added? I am not sure what "H65536" or "End(x|Up) is all about, I
get the offset info. How does Excel know to stop at the last blank?

To elaborate on the other question...I am not quite sure how else to
explain, here it goes...
When I recorded the macro I setup to autofill down the range of
F4:F1500 and O4:P1500. I only choose the 1500 because I did not know
how far down to go and I knew that 1500 rows would be more than I
needed (I am currently at 1252 rows). What I would like to know is how
to Autofill down from (F4:"to the last cell that is not blank in column
"C") and also Autofill down from O4:"to the last cell that is not blank
in column "C"). I hope this clarifies it. Thanks again for the other
info, that is what I was really after and if I am not able to
accomplish the 2nd everything will be fine.


Mark Driscol wrote:
PJ, the code

Range("H65536").End(xlUp).Offset(-1, -2).Select

will select the cell one row above and two columns to the left of the
last cell in Column H with an "x" in it.

Can you be more specific about what cells you want to do? Say there
are data in cells F1:F25 and "x"s in cells H1:H10. What specific cells
do you want selected and what cells do you want autofilled?


Mark


PJ wrote:
I am new to VBA. I recorded a macro and applied it to the command
button to fill two seperate ranges down. Now I want to edit the code to
do the following:
After I run (click the command button), the cursor stops at the cell
"F1169". I fill in the cells in column "H" with an "x" to indicate I
have finished analyizing that row. I would like the cursor to change
locations based on if the cell in column H is blank by going to the
last cell in column H with an "x" and then offset (-1,-2).
Also, when I recorded the macro, I set the range (to fill down) to
1500. I add new rows to include more data daily. The 1500# was overkill
and I only used it because I will never have that much data. I would
like to clean up the range down to stop if there is nothing in the last
row of column "C". I hope some of this makes sense. I have been
researching to find an answer on my own with no luck. Please help.

Private Sub CommandButton1_Click()
Range("F4").Select
Selection.AutoFill Destination:=Range("F4:F1500"),
Type:=xlFillDefault
Range("F4:F1500").Select
ActiveWindow.LargeScroll Down:=-42
ActiveWindow.SmallScroll Down:=-21
Range("O4:P4").Select
Selection.AutoFill Destination:=Range("O4:P1500"),
Type:=xlFillDefault
Range("O4:P1500").Select
ActiveWindow.LargeScroll Down:=-19
ActiveWindow.SmallScroll Down:=249
Range("F1169").Select
End Sub

Help stopping when row is blank
 

Range("H65536").End(xlUp).Select is equivalent to going to cell H65536
(the last possible cell in Column H), then clicking the End and up
arrow keys on keyboard. This will select the last non-blank cell in
Column H.

I think the following may be something like what you are looking for.

Option Explicit

Private Sub CommandButton1_Click()

Dim i As Long

' Select one row up and two cells to the left
' of the last cell in Column H to contain an "x"
Range("H65536").End(xlUp).Offset(-1, -2).Select

' Determine row of last cell in Column C
' which is not blank
i = Range("C65536").End(xlUp).Row

Range("F4:F" & i).FillDown
Range("O4:P" & i).FillDown

End Sub


Mark


PJ wrote:
First off....thanks a million. I added the code
"Range("H65536").End(xlUp).Offset(-1, -2).Select" and it did exactly
what I wanted it to do. In Layman's terms could you help me understand
what I added? I am not sure what "H65536" or "End(x|Up) is all about, I
get the offset info. How does Excel know to stop at the last blank?

To elaborate on the other question...I am not quite sure how else to
explain, here it goes...
When I recorded the macro I setup to autofill down the range of
F4:F1500 and O4:P1500. I only choose the 1500 because I did not know
how far down to go and I knew that 1500 rows would be more than I
needed (I am currently at 1252 rows). What I would like to know is how
to Autofill down from (F4:"to the last cell that is not blank in column
"C") and also Autofill down from O4:"to the last cell that is not blank
in column "C"). I hope this clarifies it. Thanks again for the other
info, that is what I was really after and if I am not able to
accomplish the 2nd everything will be fine.


Mark Driscol wrote:
PJ, the code

Range("H65536").End(xlUp).Offset(-1, -2).Select

will select the cell one row above and two columns to the left of the
last cell in Column H with an "x" in it.

Can you be more specific about what cells you want to do? Say there
are data in cells F1:F25 and "x"s in cells H1:H10. What specific cells
do you want selected and what cells do you want autofilled?


Mark


PJ wrote:
I am new to VBA. I recorded a macro and applied it to the command
button to fill two seperate ranges down. Now I want to edit the code to
do the following:
After I run (click the command button), the cursor stops at the cell
"F1169". I fill in the cells in column "H" with an "x" to indicate I
have finished analyizing that row. I would like the cursor to change
locations based on if the cell in column H is blank by going to the
last cell in column H with an "x" and then offset (-1,-2).
Also, when I recorded the macro, I set the range (to fill down) to
1500. I add new rows to include more data daily. The 1500# was overkill
and I only used it because I will never have that much data. I would
like to clean up the range down to stop if there is nothing in the last
row of column "C". I hope some of this makes sense. I have been
researching to find an answer on my own with no luck. Please help.

Private Sub CommandButton1_Click()
Range("F4").Select
Selection.AutoFill Destination:=Range("F4:F1500"),
Type:=xlFillDefault
Range("F4:F1500").Select
ActiveWindow.LargeScroll Down:=-42
ActiveWindow.SmallScroll Down:=-21
Range("O4:P4").Select
Selection.AutoFill Destination:=Range("O4:P1500"),
Type:=xlFillDefault
Range("O4:P1500").Select
ActiveWindow.LargeScroll Down:=-19
ActiveWindow.SmallScroll Down:=249
Range("F1169").Select
End Sub

Help stopping when row is blank
 

Okay, thanks 2 million!!
I copied your application into mine, and guess what? It works exactly
the way I want it too. Thanks for taking the time to understand my
request. What you provided really simplified all the text I had in my
original application. You're a genius!
Thank you, thank you, thank you...


Mark Driscol wrote:
Range("H65536").End(xlUp).Select is equivalent to going to cell H65536
(the last possible cell in Column H), then clicking the End and up
arrow keys on keyboard. This will select the last non-blank cell in
Column H.

I think the following may be something like what you are looking for.

Option Explicit

Private Sub CommandButton1_Click()

Dim i As Long

' Select one row up and two cells to the left
' of the last cell in Column H to contain an "x"
Range("H65536").End(xlUp).Offset(-1, -2).Select

' Determine row of last cell in Column C
' which is not blank
i = Range("C65536").End(xlUp).Row

Range("F4:F" & i).FillDown
Range("O4:P" & i).FillDown

End Sub


Mark


PJ wrote:
First off....thanks a million. I added the code
"Range("H65536").End(xlUp).Offset(-1, -2).Select" and it did exactly
what I wanted it to do. In Layman's terms could you help me understand
what I added? I am not sure what "H65536" or "End(x|Up) is all about, I
get the offset info. How does Excel know to stop at the last blank?

To elaborate on the other question...I am not quite sure how else to
explain, here it goes...
When I recorded the macro I setup to autofill down the range of
F4:F1500 and O4:P1500. I only choose the 1500 because I did not know
how far down to go and I knew that 1500 rows would be more than I
needed (I am currently at 1252 rows). What I would like to know is how
to Autofill down from (F4:"to the last cell that is not blank in column
"C") and also Autofill down from O4:"to the last cell that is not blank
in column "C"). I hope this clarifies it. Thanks again for the other
info, that is what I was really after and if I am not able to
accomplish the 2nd everything will be fine.


Mark Driscol wrote:
PJ, the code

Range("H65536").End(xlUp).Offset(-1, -2).Select

will select the cell one row above and two columns to the left of the
last cell in Column H with an "x" in it.

Can you be more specific about what cells you want to do? Say there
are data in cells F1:F25 and "x"s in cells H1:H10. What specific cells
do you want selected and what cells do you want autofilled?


Mark


PJ wrote:
I am new to VBA. I recorded a macro and applied it to the command
button to fill two seperate ranges down. Now I want to edit the code to
do the following:
After I run (click the command button), the cursor stops at the cell
"F1169". I fill in the cells in column "H" with an "x" to indicate I
have finished analyizing that row. I would like the cursor to change
locations based on if the cell in column H is blank by going to the
last cell in column H with an "x" and then offset (-1,-2).
Also, when I recorded the macro, I set the range (to fill down) to
1500. I add new rows to include more data daily. The 1500# was overkill
and I only used it because I will never have that much data. I would
like to clean up the range down to stop if there is nothing in the last
row of column "C". I hope some of this makes sense. I have been
researching to find an answer on my own with no luck. Please help.

Private Sub CommandButton1_Click()
Range("F4").Select
Selection.AutoFill Destination:=Range("F4:F1500"),
Type:=xlFillDefault
Range("F4:F1500").Select
ActiveWindow.LargeScroll Down:=-42
ActiveWindow.SmallScroll Down:=-21
Range("O4:P4").Select
Selection.AutoFill Destination:=Range("O4:P1500"),
Type:=xlFillDefault
Range("O4:P1500").Select
ActiveWindow.LargeScroll Down:=-19
ActiveWindow.SmallScroll Down:=249
Range("F1169").Select
End Sub

Help stopping when row is blank
 

You are very welcome. There are a lot of REALLY smart people who
answer the questions I post here, so I am glad to help someone out in
return.

Mark


PJ wrote:
Okay, thanks 2 million!!
I copied your application into mine, and guess what? It works exactly
the way I want it too. Thanks for taking the time to understand my
request. What you provided really simplified all the text I had in my
original application. You're a genius!
Thank you, thank you, thank you...


Mark Driscol wrote:
Range("H65536").End(xlUp).Select is equivalent to going to cell H65536
(the last possible cell in Column H), then clicking the End and up
arrow keys on keyboard. This will select the last non-blank cell in
Column H.

I think the following may be something like what you are looking for.

Option Explicit

Private Sub CommandButton1_Click()

Dim i As Long

' Select one row up and two cells to the left
' of the last cell in Column H to contain an "x"
Range("H65536").End(xlUp).Offset(-1, -2).Select

' Determine row of last cell in Column C
' which is not blank
i = Range("C65536").End(xlUp).Row

Range("F4:F" & i).FillDown
Range("O4:P" & i).FillDown

End Sub


Mark


PJ wrote:
First off....thanks a million. I added the code
"Range("H65536").End(xlUp).Offset(-1, -2).Select" and it did exactly
what I wanted it to do. In Layman's terms could you help me understand
what I added? I am not sure what "H65536" or "End(x|Up) is all about, I
get the offset info. How does Excel know to stop at the last blank?

To elaborate on the other question...I am not quite sure how else to
explain, here it goes...
When I recorded the macro I setup to autofill down the range of
F4:F1500 and O4:P1500. I only choose the 1500 because I did not know
how far down to go and I knew that 1500 rows would be more than I
needed (I am currently at 1252 rows). What I would like to know is how
to Autofill down from (F4:"to the last cell that is not blank in column
"C") and also Autofill down from O4:"to the last cell that is not blank
in column "C"). I hope this clarifies it. Thanks again for the other
info, that is what I was really after and if I am not able to
accomplish the 2nd everything will be fine.


Mark Driscol wrote:
PJ, the code

Range("H65536").End(xlUp).Offset(-1, -2).Select

will select the cell one row above and two columns to the left of the
last cell in Column H with an "x" in it.

Can you be more specific about what cells you want to do? Say there
are data in cells F1:F25 and "x"s in cells H1:H10. What specific cells
do you want selected and what cells do you want autofilled?


Mark


PJ wrote:
I am new to VBA. I recorded a macro and applied it to the command
button to fill two seperate ranges down. Now I want to edit the code to
do the following:
After I run (click the command button), the cursor stops at the cell
"F1169". I fill in the cells in column "H" with an "x" to indicate I
have finished analyizing that row. I would like the cursor to change
locations based on if the cell in column H is blank by going to the
last cell in column H with an "x" and then offset (-1,-2).
Also, when I recorded the macro, I set the range (to fill down) to
1500. I add new rows to include more data daily. The 1500# was overkill
and I only used it because I will never have that much data. I would
like to clean up the range down to stop if there is nothing in the last
row of column "C". I hope some of this makes sense. I have been
researching to find an answer on my own with no luck. Please help.

Private Sub CommandButton1_Click()
Range("F4").Select
Selection.AutoFill Destination:=Range("F4:F1500"),
Type:=xlFillDefault
Range("F4:F1500").Select
ActiveWindow.LargeScroll Down:=-42
ActiveWindow.SmallScroll Down:=-21
Range("O4:P4").Select
Selection.AutoFill Destination:=Range("O4:P1500"),
Type:=xlFillDefault
Range("O4:P1500").Select
ActiveWindow.LargeScroll Down:=-19
ActiveWindow.SmallScroll Down:=249
Range("F1169").Select
End Sub