Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find next blank row
Hi i have been using this macro to cut and paste data from one sheet to another when i run the macro the first time it works fine but then i run the macro the second time the macro over rides my data how do i make it so that data is pasted into the next available blank row in sheet 2 Code: -------------------- Sub SearchForString() Dim LSearchRow As Integer Dim LCopyToRow As Integer On Error GoTo Err_Execute 'Start search in row 4 LSearchRow = 2 'Start copying data to row 2 in Sheet2 (row counter variable) LCutToRow = 2 While Len(Range("F" & CStr(LSearchRow)).Value) 0 If Range("F" & CStr(LSearchRow)).Value = "RG Complete" Then Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select Selection.Cut Sheets("Sheet2").Select Rows(CStr(LCutToRow) & ":" & CStr(LCutToRow)).Select ActiveSheet.Paste LCutToRow = LCutToRow + 1 'Go back to Sheet1 to continue searching Sheets("Sheet1").Select End If LSearchRow = LSearchRow + 1 Wend 'Position on cell A3 Application.CutCopyMode = False Application.ScreenUpdating = False Range("F2").Select MsgBox "All RG Complete data has been copied." Call macro DeleteBlankRows Exit Sub Err_Execute: MsgBox "An error occurred." End Sub -------------------- -- Vlad999 ------------------------------------------------------------------------ Vlad999's Profile: http://www.excelforum.com/member.php...o&userid=33586 View this thread: http://www.excelforum.com/showthread...hreadid=556718 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find next blank row
Anyone? It shouldn't be that hard should it? -- Vlad999 ------------------------------------------------------------------------ Vlad999's Profile: http://www.excelforum.com/member.php...o&userid=33586 View this thread: http://www.excelforum.com/showthread...hreadid=556718 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find next blank row
Hello Vlad999, Change... 'Start copying data to row 2 in Sheet2 (row counter variable) LCutToRow = 2 To this... With Sheets("Sheet2") LCutToRow = .Cells(.Rows.Count, "F").Row + 1 End With This will return the next blank row in column "F" on Sheet2. Sincerely. Leith Ross -- Leith Ross ------------------------------------------------------------------------ Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465 View this thread: http://www.excelforum.com/showthread...hreadid=556718 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find next blank row
Thank you :) -- Vlad999 ------------------------------------------------------------------------ Vlad999's Profile: http://www.excelforum.com/member.php...o&userid=33586 View this thread: http://www.excelforum.com/showthread...hreadid=556718 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find First Non blank cell than find column header and return that value | Excel Worksheet Functions | |||
Find second blank row | Excel Programming | |||
Find second blank row | Excel Discussion (Misc queries) | |||
VB: Find blank row then add another blank row | Excel Programming | |||
VB: How do I find next blank row then add another blank row | Excel Programming |