Excel 2007 "If....Then select Row and move&qu
Solved/Closed
Matt
-
Feb 2, 2010 at 09:04 PM
rizvisa1
rizvisa1
- Posts
- 4479
- Registration date
- Thursday January 28, 2010
- Status
- Contributor
- Last seen
- May 5, 2022
Related:
- Excel 2007 "If....Then select Row and move&qu
- Macros to Delete Rows in Excel 2007 ✓ - Forum - Programming
- Selecting a printer in an Excel 2007 Userform ✓ - Forum - Excel
- Excel Macro: combine selected rows ✓ - Forum - Excel
- Excel transfer selected rows ✓ - Forum - Excel
- Conditionally deleting rows in excel 2007 ✓ - Forum - Excel
7 replies
rizvisa1
Feb 3, 2010 at 06:12 AM
- Posts
- 4479
- Registration date
- Thursday January 28, 2010
- Status
- Contributor
- Last seen
- May 5, 2022
Feb 3, 2010 at 06:12 AM
Does it have to be auto job or could you live with manually firing it?
cujo
Feb 3, 2010 at 10:41 AM
- Posts
- 4
- Registration date
- Tuesday February 2, 2010
- Status
- Member
- Last seen
- February 3, 2010
Feb 3, 2010 at 10:41 AM
I wouldn't have a problem with manually firing it off. Just being able to clear completed lines so that other "high-priority jobs" could be moved to the top is what I'm shooting for.
Thanks much!
Matt
Thanks much!
Matt
rizvisa1
Feb 3, 2010 at 11:30 AM
- Posts
- 4479
- Registration date
- Thursday January 28, 2010
- Status
- Contributor
- Last seen
- May 5, 2022
Feb 3, 2010 at 11:30 AM
Well see if this works for you
Assumption
Main Sheet is called WIP
Archive Sheet is called Done
Presumption is that there are no more than 26 column in the WIP and Done Sheets
Steps:
1. Press ALT + F11 to launch VBE
2. Click on insert an Add a new module
3. Copy the code below and paste on the new module
4. To run the code, (click F5)
Assumption
Main Sheet is called WIP
Archive Sheet is called Done
Presumption is that there are no more than 26 column in the WIP and Done Sheets
Steps:
1. Press ALT + F11 to launch VBE
2. Click on insert an Add a new module
3. Copy the code below and paste on the new module
4. To run the code, (click F5)
Sub cleanup() Dim iMaxCol As Integer iMaxCol = 26 Sheets("WIP").Select If ActiveSheet.AutoFilterMode Then Cells.Select Selection.AutoFilter End If Cells.Select If ActiveSheet.AutoFilterMode = False Then Selection.AutoFilter End If Selection.AutoFilter Field:=19, Criteria1:="=" & 1, Operator:=xlAnd ilastrow = Cells(Rows.Count, 1).End(xlUp).Row For iCol = 2 To iMaxCol tMaxRow = Cells(Rows.Count, iCol).End(xlUp).Row If (tMaxRow > ilastrow) Then ilastrow = tMaxRow Next If (ilastrow = 1) Then MsgBox ("Get back to work, nothing is done yet!!!") If ActiveSheet.AutoFilterMode Then Cells.Select Selection.AutoFilter End If Exit Sub End If If Sheets("Done").AutoFilterMode Then Sheets("Done").Cells.Select Sheets("Done").Selection.AutoFilter End If iMaxDone = Sheets("Done").Cells(Rows.Count, iCol).End(xlUp).Row For iCol = 2 To iMaxCol tMaxRow = Sheets("Done").Cells(Rows.Count, iCol).End(xlUp).Row If (tMaxRow > iMaxDone) Then iMaxDone = tMaxRow Next If iMaxDone < 2 Then iMaxDone = 1 iMaxDone = iMaxDone + 1 'Rows("2:Range(Cells(2, 1), Cells(ilastrow, iMaxCol)).Select Rows("2:" & ilastrow).Select Selection.Copy Sheets("Done").Select Cells(iMaxDone, 1).Select Selection.PasteSpecial Sheets("WIP").Select Rows("2:" & ilastrow).Delete If ActiveSheet.AutoFilterMode Then Cells.Select Selection.AutoFilter End If End Sub
Hi Rizvisa1
I am having similar need & having same problem described by cujo, I understood these codes but aint know nothing how to use these, I saved my worksheet as micro enabled & created a macro named "cleanup" & then simply pressed Alt+f11, then tried pasting above codes in module, these sheet & "WIP" as well as "Done sheet, I am using excel 2007,
please guide me how to use these codes, assuming I am not programer but a excel userwho need these for my automation need, I wud realy appreciate ur help sir .. !!
I am having similar need & having same problem described by cujo, I understood these codes but aint know nothing how to use these, I saved my worksheet as micro enabled & created a macro named "cleanup" & then simply pressed Alt+f11, then tried pasting above codes in module, these sheet & "WIP" as well as "Done sheet, I am using excel 2007,
please guide me how to use these codes, assuming I am not programer but a excel userwho need these for my automation need, I wud realy appreciate ur help sir .. !!
rizvisa1
May 31, 2010 at 09:07 AM
- Posts
- 4479
- Registration date
- Thursday January 28, 2010
- Status
- Contributor
- Last seen
- May 5, 2022
May 31, 2010 at 09:07 AM
I modified the code and instruction. See if that helps you
rizvisa1
Jun 1, 2010 at 07:22 AM
- Posts
- 4479
- Registration date
- Thursday January 28, 2010
- Status
- Contributor
- Last seen
- May 5, 2022
Jun 1, 2010 at 07:22 AM
possible but results can be unexpected. So I would recommend that you do it manually.
cujo
Feb 3, 2010 at 02:08 PM
- Posts
- 4
- Registration date
- Tuesday February 2, 2010
- Status
- Member
- Last seen
- February 3, 2010
Feb 3, 2010 at 02:08 PM
It appears my limited knowledge on the high end programming for Excel 2007 can't compare with most of you. Per your recommendation, I have renamed the sheets accordingly, and copied the code. I assumed this went in the visual basic screen and copied into "This Workbook". When that didn't work on a test, I then added the code to both schets (WIP and Done).
Again, I have a feeling because I've never gotten this granular before in Excel, it's a problem on my part.
If anyone wants to look at the file and give me your thoughts, here's a link to download:
https://www.sendspace.com/file/uag0g6
Thanks again to all of you. Now I realize why I stick doing my job, and never attempted to go into programming! ;)
Matt
Again, I have a feeling because I've never gotten this granular before in Excel, it's a problem on my part.
If anyone wants to look at the file and give me your thoughts, here's a link to download:
https://www.sendspace.com/file/uag0g6
Thanks again to all of you. Now I realize why I stick doing my job, and never attempted to go into programming! ;)
Matt
Didn't find the answer you are looking for?
Ask a question
cujo
Feb 3, 2010 at 02:17 PM
- Posts
- 4
- Registration date
- Tuesday February 2, 2010
- Status
- Member
- Last seen
- February 3, 2010
Feb 3, 2010 at 02:17 PM
For whatever reason my previous posts didn't attach to the thread.
I attempted to attach the file to the thread as well, but I'm having problems with that as well.
I have a feeling that because of my limited knowledge at this level of programming, I've done something wrong. Per your suggestion, I have renamed the sheets, and added the code in the visual basic editor. When it didn't propertly work on a test line, I then added the code not only in the "This Workbook" along with the "WIP" sheet and "Done" sheet.
If any of you have a suggestion how I can attach the file to this thread, I'd be more than happy to post it for eveyone's thoughts.
Thanks again!
Matt
I attempted to attach the file to the thread as well, but I'm having problems with that as well.
I have a feeling that because of my limited knowledge at this level of programming, I've done something wrong. Per your suggestion, I have renamed the sheets, and added the code in the visual basic editor. When it didn't propertly work on a test line, I then added the code not only in the "This Workbook" along with the "WIP" sheet and "Done" sheet.
If any of you have a suggestion how I can attach the file to this thread, I'd be more than happy to post it for eveyone's thoughts.
Thanks again!
Matt
aquarelle
Feb 3, 2010 at 02:30 PM
- Posts
- 7099
- Registration date
- Saturday April 7, 2007
- Status
- Moderator
- Last seen
- January 20, 2022
Feb 3, 2010 at 02:30 PM
Hello,
I restore the above message thus now you can see the link to download the excel file.
Good continuation
I restore the above message thus now you can see the link to download the excel file.
Good continuation
cujo
Feb 3, 2010 at 03:21 PM
- Posts
- 4
- Registration date
- Tuesday February 2, 2010
- Status
- Member
- Last seen
- February 3, 2010
Feb 3, 2010 at 03:21 PM
Ha! I even surprise myself. A small little IT10T error on my part caused the problem.
Thanks greatly..This does exactly what I need to have done. Kudos to you!
Matt
Thanks greatly..This does exactly what I need to have done. Kudos to you!
Matt