Note: The other languages of the website are Google-translated. Back to English
Logáil isteach  \/ 
x
or
x
Cláraigh  \/ 
x

or

Conas an tsraith iomlán a bhogadh go bun an bhileog ghníomhaigh bunaithe ar luach cille in Excel?

Chun an tsraith iomlán a bhogadh go bun na bileoige gníomhaí bunaithe ar luach cille in Excel, bain triail as an gcód VBA san alt seo.

Bog an tsraith iomlán go bun an bhileog ghníomhaigh bunaithe ar luach cille le cód VBA


Bog an tsraith iomlán go bun an bhileog ghníomhaigh bunaithe ar luach cille le cód VBA


Mar shampla, mar a thaispeántar thíos an scáileán, má tá luach áirithe “Déanta” i gcill i gcolún C, ansin bog an tsraith iomlán go bun an bhileog reatha. Déan mar a leanas le do thoil.

1. Brúigh Eile+ F11 eochracha ag an am céanna chun an Microsoft Visual Basic d’Fheidhmchláir fhuinneog.

2. Sa Microsoft Visual Basic d’Fheidhmchláir fuinneog, cliceáil Ionsáigh > Modúil. Ansin cóipeáil agus greamaigh an cód VBA thíos san fhuinneog.

Cód VBA: Bog an tsraith iomlán go bun an bhileog ghníomhaigh bunaithe ar luach na cille

Sub MoveToEnd()
    Dim xRg As Range
    Dim xTxt As String
    Dim xCell As Range
    Dim xEndRow As Long
    Dim I As Long
    On Error Resume Next
    If ActiveWindow.RangeSelection.Count > 1 Then
      xTxt = ActiveWindow.RangeSelection.AddressLocal
    Else
      xTxt = ActiveSheet.UsedRange.AddressLocal
    End If
lOne:
    Set xRg = Application.InputBox("Select range:", "Kutools for Excel", xTxt, , , , , 8)
    If xRg Is Nothing Then Exit Sub
    If xRg.Columns.Count > 1 Or xRg.Areas.Count > 1 Then
        MsgBox " Multiple ranges or columns have been selected ", vbInformation, "Kutools for Excel"
        GoTo lOne
    End If
    xEndRow = xRg.Rows.Count + xRg.Row
    Application.ScreenUpdating = False
    For I = xRg.Rows.Count To 1 Step -1
        If xRg.Cells(I) = "Done" Then
           xRg.Cells(I).EntireRow.Cut
           Rows(xEndRow).Insert Shift:=xlDown
        End If
    Next
    Application.ScreenUpdating = True
End Sub

nótaí: Sa chód VBA, “Arna dhéanamh"Is é an luach cille a bhogfaidh tú as a chéile bunaithe ar. Is féidir leat é a athrú de réir mar is gá duit.

3. Brúigh an F5 eochair chun an cód a rith, ansin sa phreabadh suas Kutools le haghaidh Excel bosca dialóige, roghnaigh an raon colún a bhfuil an luach áirithe ann, ansin cliceáil ar an OK cnaipe.

Tar éis a chliceáil ar an OK cnaipe, bogtar an tsraith iomlán ina bhfuil an luach “Arna dhéanamh” sa cholún sonraithe go bun an raon sonraí go huathoibríoch.


Earraí gaolmhara:


Na hUirlisí Táirgiúlachta Oifige is Fearr

Réitíonn Kutools for Excel an chuid is mó de do chuid Fadhbanna, agus Méadaíonn sé do Tháirgiúlacht 80%

  • Athúsáid: Cuir isteach go tapa foirmlí casta, cairteacha agus aon rud a d'úsáid tú roimhe seo; Cealla a Chriptiú le pasfhocal; Cruthaigh Liosta Ríomhphoist agus seol ríomhphoist ...
  • Barra Foirmle Super (cuir línte iolracha téacs agus foirmle in eagar go héasca); Leagan Amach Léitheoireachta (líon mór cealla a léamh agus a chur in eagar go héasca); Greamaigh go dtí an Raon Scagtha...
  • Cumaisc Cealla / Sraitheanna / Colúin gan Sonraí a chailleadh; Ábhar Cealla Scoilt; Comhcheangail Sraitheanna / Colúin Dúblacha... Cill Dúblach a Chosc; Déan comparáid idir Ranganna...
  • Roghnaigh Dúblach nó Uathúil Sraitheanna; Roghnaigh Blank Rows (tá na cealla uile folamh); Aimsigh Super agus Fuzzy Aimsigh i go leor Leabhar Oibre; Roghnaigh go randamach ...
  • Cóip Díreach Cealla Il gan tagairt fhoirmle a athrú; Tagairtí Cruthaigh Auto chuig Bileoga Il; Cuir Urchair isteach, Boscaí Seiceála agus go leor eile ...
  • Sliocht Téacs, Cuir Téacs leis, Bain de réir Poist, Bain Spás; Subtotals Paging a chruthú agus a phriontáil; Tiontaigh Idir Ábhar Cealla agus Tráchtanna...
  • Scagaire Super (scéimeanna scagaire a shábháil agus a chur i bhfeidhm ar bhileoga eile); Ard-Sórtáil de réir míosa / seachtaine / lae, minicíocht agus níos mó; Scagaire Speisialta le cló trom, iodálach ...
  • Comhcheangail Leabhair Oibre agus Bileoga Oibre; Cumaisc Táblaí bunaithe ar eochaircholúin; Roinn Sonraí i Ilbhileoga; Baisc Tiontaigh xls, xlsx agus PDF...
  • Níos mó ná 300 gné chumhachtach. Tacaíochtaí Office / Excel 2007-2019 agus 365. Tacaíonn sé le gach teanga. Imscaradh éasca i d’fhiontar nó d’eagraíocht. Gnéithe iomlána triail saor in aisce 30 lá. Ráthaíocht ar ais airgid 60 lá.
cluaisín kte 201905

Tugann Tab Oifige comhéadan Tabbed chuig Office, agus Déan Do Obair i bhfad Níos Éasca

  • Cumasaigh eagarthóireacht agus léamh tabbed i Word, Excel, PowerPoint, Foilsitheoir, Rochtain, Visio agus Tionscadal.
  • Oscail agus cruthaigh cáipéisí iolracha i gcluaisíní nua den fhuinneog chéanna, seachas i bhfuinneoga nua.
  • Méadaíonn do tháirgiúlacht 50%, agus laghdaíonn sé na céadta cad a tharlaíonn nuair luch duit gach lá!
bun officetab
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Kandice · 4 months ago
    I have a list with check boxes that when one column is checked I need it to go to one section of the spreadsheet and if the other is checked instead it goes to the end.  I have tried a hundred diff ways to do this can anyone help with this?
  • To post as a guest, your comment is unpublished.
    Brett Meehan · 7 months ago
    Hi Crystal,

    Thanks for you help, the code works great but rather than move the row to the bottom of a page how to I move it to another tab i.e. a "Closed" tab?
  • To post as a guest, your comment is unpublished.
    VikasExcelinExcel · 7 months ago
    How do it if "Done" is only a part of a column string. Suppose my Columns contains value like - XYZDone, ABCDone, 123Done etc, can I just filter out based on partial string "Done"?
  • To post as a guest, your comment is unpublished.
    jasnoke · 11 months ago
    Hello, I have a task my boss has given me. It seemed simple enough at first but now I am confused as how to proceed. We have a Forecast sheet of the possible jobs and they have a "Order Probability" column by %. He wants me to set up 3 different sheets with 100-70%, 69%-41, and 40-0%. The idea is that as the information is typed inot the master sheet, when the percentage is entered in, it automatically gets copied into the proceeding sheet matching that percentile rage. I did this with a simple IF(and formula. However i need to sort inorder to loose the empty cells and make it look cleaner. Then when i sort , if i add a new Oder Probability offer to the master sheet, it does not automatically show it, without unsorting then sorting again. I apologize if this question does not belong her. But is there a string of code i could put in that would handle this issue easier? The only value that determines if the whole row gets moved is the K column. seems simple, but however complex for this excel beginner. Thanks in advance for your help.
  • To post as a guest, your comment is unpublished.
    awiesner7482 · 1 years ago
    Hello,

    I am having issues using the code provided and keep receiving a syntax error. I am super new to excel and have been trying to self teach what I need to run my home business. I have an inventory spreadsheet id like to be able to designate items in a column as RETIRED? yes/no and if yes, they move to the bottom of the sheet, in alphabetical order, without leaving a blank space in the main spread sheet. we have items retire completely and then come back for a special re-release in limited quantities and id like these items stored at the bottom of my sheet until they become available again. Thank you.
  • To post as a guest, your comment is unpublished.
    Jerel · 1 years ago
    What if you only need to move rows under Column A and B; then Column C should retain? should we still use EntireRow?
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Hi Jerel,
      Try the below code. Hope I can help.

      Sub MoveToEnd()
      'Updated by Extendoffice 20200717
      Dim xRg As Range
      Dim xTxt As String
      Dim xCell As Range
      Dim xEndRow As Long
      Dim xIntR As Integer
      Dim I As Long
      Dim xWs As Worksheet
      On Error Resume Next
      If ActiveWindow.RangeSelection.Count > 1 Then
      xTxt = ActiveWindow.RangeSelection.AddressLocal
      Else
      xTxt = ActiveSheet.UsedRange.AddressLocal
      End If
      lOne:
      Set xRg = Application.InputBox("Select range:", "Kutools for Excel", xTxt, , , , , 8)
      If xRg Is Nothing Then Exit Sub
      If xRg.Columns.Count > 1 Or xRg.Areas.Count > 1 Then
      MsgBox " Multiple ranges or columns have been selected ", vbInformation, "Kutools for Excel"
      GoTo lOne
      End If
      xEndRow = xRg.Rows.Count + xRg.Row
      xWs = xRg.Worksheet
      xWs.Activate
      Application.ScreenUpdating = False
      For I = xRg.Rows.Count To 1 Step -1
      If xRg.Item(I) = "Done" Then
      Rows(xEndRow).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
      xIntR = xRg.Cells(I).Row
      Range("A" & xIntR & ":B" & xIntR).Select
      Selection.Cut
      Range("A" & xEndRow).Select
      ActiveSheet.Paste
      xEndRow = xEndRow + 1

      End If
      Next
      Application.ScreenUpdating = True
      End Sub
  • To post as a guest, your comment is unpublished.
    sarah · 2 years ago
    hello crystal the code u gave to anon to have the vba run code automatically is great but i cant insert a sheet of rows is there a possible way to fix it
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Hi sarah,
      Sorry for the inconvenience. Please try the below VBA. Thanks.

      Private Sub Worksheet_Change(ByVal Target As Range)
      'Updated by Extendoffice 20200424
      Dim xRg As Range
      Dim xIRg As Range
      Dim xTxt As String
      Dim xCell As Range
      Dim xEndRow As Long
      Dim I As Long
      Dim xDStr As String
      On Error GoTo Err1
      xDStr = "C:C"
      Set xRg = Me.Range(xDStr)
      Set xIRg = Application.Intersect(Target, xRg)
      If xIRg Is Nothing Then Exit Sub
      Application.ScreenUpdating = False
      Application.EnableEvents = False

      If Target.Value = "Done" Then
      'xEndRow = ActiveSheet.UsedRange.Rows.Count + 1
      xEndRow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row + 1
      Target.EntireRow.Cut
      Rows(xEndRow).Insert Shift:=xlDown
      End If
      Err1:
      Application.EnableEvents = True
      Application.ScreenUpdating = True
      End Sub
      • To post as a guest, your comment is unpublished.
        Michael · 5 months ago
        I tried to copy this code but it continues to say invalid use of Me.
  • To post as a guest, your comment is unpublished.
    Anon · 2 years ago
    Hello, the only way I can get this to work is if I "run" the macro in the Visual Basic sub screen. Is it possible to have this VBA code run automatically, once the user types in "Done"? Each time I'm getting the Kutools for Excel dialog box pop up to ask the parameters I'm requesting the code to search by. I have sort of worked around this by replacing : xTxt = ActiveSheet.UsedRange.AddressLocal with the parameters I need searched and hitting enter. But it would be more convenient if it automatically made the changes after "Done" was entered. Thank you!
    • To post as a guest, your comment is unpublished.
      crystal · 2 years ago
      Hi Anon,
      The below VBA code can do you a favor. Please have a try.
      Please right click the sheet tab (the sheet contains the data you will move to bottom), select View Code from the context menu and copy the below code into the Code window.

      Private Sub Worksheet_Change(ByVal Target As Range)
      'Updated by Extendoffice 20190925
      Dim xRg As Range
      Dim xIRg As Range
      Dim xTxt As String
      Dim xCell As Range
      Dim xEndRow As Long
      Dim I As Long
      Dim xDStr As String
      On Error Resume Next
      xDStr = "C:C"
      Set xRg = Me.Range(xDStr)
      Set xIRg = Application.Intersect(Target, xRg)
      If xIRg Is Nothing Then Exit Sub
      Application.ScreenUpdating = False
      Application.EnableEvents = False

      If Target = "Done" Then
      xEndRow = ActiveSheet.UsedRange.Rows.Count + 1
      Target.EntireRow.Cut
      Rows(xEndRow).Insert Shift:=xlDown
      End If
      Application.EnableEvents = True
      Application.ScreenUpdating = True
      End Sub
      • To post as a guest, your comment is unpublished.
        tbsinc · 2 years ago
        Hi crystal,

        Thank you for posting this code. I would like to know how to get the code to move the row back to the top if Done was typed in error. Could a secondary code be added for "move" to move it to the top, and "done" for the bottom?
  • To post as a guest, your comment is unpublished.
    Tammy · 2 years ago
    Hello, is there a way to tweak this so that it moves a row somewhere else in the same sheet besides the end? I have a sheet that has order information for dated orders and upcoming orders yet to be dated and I have it so that when I put an "X" in column A things highlight and bold depending on part #'s and shipping locations. Now I have to physically cut and paste the newly dated (shipping date) so that it fits in at the top sorted by date (1st thru end of month). I have been able to conditional format everything up to this point, but I don't think I can move rows that way. I was wondering if VBA could do this, move a row when a date is entered to fit in with the other dated rows?
  • To post as a guest, your comment is unpublished.
    hendrix56 · 3 years ago
    Hello. This is almost perfect for what I want. I have part of the same request as Anon had to make this work without user input along with some extras.

    I am wondering if it is possible to have the only column searched to be i4 to i50 and have it automatically run at open or anytime the i column is edited. Also if it would be possible to move the rows to the bottom of the sheet without any blank rows between "done" rows and "no" rows. Currently if I select i4:i50 and if I only have data up to row 25 it will paste the "done" rows ascending from row 50 instead of row 25. The number of rows in my sheet is constantly changing and shouldn't get to more than 50. Thanks for the help.
    • To post as a guest, your comment is unpublished.
      crystal · 2 years ago
      Hi Brandon,
      Sorry can help you with that. Thank you for your comment.
  • To post as a guest, your comment is unpublished.
    Mags · 3 years ago
    Hi Crystal, this code works great, thank you. I would like move all the rows which contain word "Complete" in column D to the top of the table (insert in row 3). Is that possible? then I would like to delete all these complete rows which contain "yesterday date" in column V.
  • To post as a guest, your comment is unpublished.
    Anon · 3 years ago
    How could I make it so the kutools selects specific rows without user input?
    • To post as a guest, your comment is unpublished.
      crystal · 3 years ago
      Hi Anon,
      I am sorry, I'm not sure what you mean. Would be nice if you can explain it again or provide a screenshot to show what you are trying to do .
      Thanks for your comment.