Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

Excel 2007 "If....Then select Row and move&qu [Solved/Closed]

Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 -
Hi all!

I have what would appear to be a basic question, but can't seem to put my brain around the solution.

I have a spreadsheet on Sheet 1. One of the cells in each row is a validation cell for me, which (based on whether any data is in another cell), spits out a "1" if the job is complete.

I'd like to know how I can tell Excel that if as "1" appears in cell S2, to select the entire row, and move to another sheet, which will compile all our completed jobs.

If anybody has suggestions, they are greatly appreciated!

Thanks!

7 replies

Answer 1 / 7
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 767
Does it have to be auto job or could you live with manually firing it?
1
Thank you
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Answer 2 / 7
Posts
4
Registration date
Tuesday February 2, 2010
Status
Member
Last seen
February 3, 2010
 4
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
1
Thank you
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Answer 3 / 7
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 767
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) 

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
1
Thank you
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Report

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 .. !!
Report
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 767
I modified the code and instruction. See if that helps you
Report

yep .. its works ..... u r a real genious .. gr8

jst 1 more help plz, can I make that macro autorun whenevr any value gets updated in the WIP sheet?
Report
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 767
possible but results can be unexpected. So I would recommend that you do it manually.
Answer 4 / 7
Posts
4
Registration date
Tuesday February 2, 2010
Status
Member
Last seen
February 3, 2010
 4
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
1
Thank you
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Answer 5 / 7
Posts
4
Registration date
Tuesday February 2, 2010
Status
Member
Last seen
February 3, 2010
 4
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
1
Thank you
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Answer 6 / 7
Posts
7096
Registration date
Saturday April 7, 2007
Status
Moderator
Last seen
February 22, 2021
 483
Hello,

I restore the above message thus now you can see the link to download the excel file.

Good continuation
1
Thank you
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Answer 7 / 7
Posts
4
Registration date
Tuesday February 2, 2010
Status
Member
Last seen
February 3, 2010
 4
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
1
Thank you
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Ask a question

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!