Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Stopping IF command from entering zero or 1 for blank cells | Excel Worksheet Functions | |||
Stopping blank page printing | Charts and Charting in Excel | |||
Stopping blank cells with formulae in from printing | Excel Programming | |||
counting from left to right and stopping when blank | Excel Discussion (Misc queries) | |||
Stopping chart line where 1 of 2 columns of data in calculation is blank | Charts and Charting in Excel |