Namig: drugi jeziki so prevedeni v Google. Lahko obiščete English različico te povezave.
Vpiši se
x
or
x
x
Registracija
x

or

Kako premakniti celotno vrstico na dno aktivne strani glede na vrednost celice v Excelu?

Za premik celotne vrstice na dno aktivne strani glede na vrednost celice v Excelu, poskusite kodo VBA v tem članku.

Premaknite celotno vrstico na dno aktivne strani glede na vrednost celice s kodo VBA


puščica modra desno mehurček Premaknite celotno vrstico na dno aktivne strani glede na vrednost celice s kodo VBA


Na primer, kot je prikazano na sliki spodaj, če celica v stolpcu C vsebuje določeno vrednost »Done«, nato pa celotno vrstico premaknite na dno trenutnega lista. Prosimo, naredite na naslednji način.

1. Pritisnite druga+ F11 tipke hkrati odpreti Microsoft Visual Basic za aplikacije okno.

2. V Ljubljani Microsoft Visual Basic za aplikacije okno, kliknite Vstavi > Moduli. Nato kopirajte in prilepite spodnjo kodo VBA v okno.

VBA koda: premaknite celotno vrstico na dno aktivne strani glede na vrednost celice

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

Opombe: V kodi VBA, "Done"Je vrednost celice, na kateri boste premaknili celotno vrstico na podlagi. Lahko ga spremenite, kot jo potrebujete.

3. Pritisnite F5 ključ za zagon kode, nato pa pri pojavu Kutools za Excel pogovorno okno, izberite obseg stolpcev, v katerem obstaja določena vrednost, nato kliknite OK gumb.

Po kliku OK gumb, celotna vrstica, ki vsebuje vrednost »Končano« v določenem stolpcu, se samodejno premakne na dno podatkovnega območja.


puščica modra desno mehurčekSorodni članki:


Priporočena orodja za produktivnost za Excel

zavihek kte 201905

Kutools za Excel vam pomaga, da vedno končate delo pred časom in izstopite iz množice

  • Več kot zmogljive napredne funkcije 300, zasnovane za 1500 delovne scenarije, ki povečujejo produktivnost z 70%, vam dajejo več časa za skrb za družino in uživanje v življenju.
  • Ne potrebujete več pomnilniških formul in VBA kod, od zdaj naprej pa dajate svojim možganom počitek.
  • Postanite strokovnjak za Excel v minutah 3, zapletene in ponavljajoče se operacije lahko opravite v nekaj sekundah,
  • Vsak dan zmanjšajte število operacij tipkovnice in miške, zdaj se poslovite od poklicnih bolezni.
  • 110,000 visoko učinkovite ljudi in 300 + svetovno priznanih podjetij izbiro.
  • Brezplačna preizkusna različica 60 dneva. 60-dnevno jamstvo vračila denarja. 2 let brezplačne nadgradnje in podpore.

Prinaša kartično brskanje in urejanje za Microsoft Office, veliko močnejši od zavihkov brskalnika

  • Office Tab je namenjen za Word, Excel, PowerPoint in druge Office aplikacije: Založnik, Dostop, Visio in Projekt.
  • Odprite in ustvarite več dokumentov v novih zavihkih istega okna in ne v novih oknih.
  • Z 50% poveča vašo produktivnost in vsak dan zmanjša na stotine klikov z miško!
Say something here...
symbols left.
You are guest ( Sign Up? )
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.
    Tammy · 7 days 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.
    Brandon · 5 months 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 · 3 months ago
      Hi Brandon,
      Sorry can help you with that. Thank you for your comment.
  • To post as a guest, your comment is unpublished.
    Mags · 10 months 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 · 1 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 · 10 months 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.