Good day, There is a table, in the first column are several lists with caps. How to sort each list separately using VBA? The ranges of the lists are constantly changing, so you can’t set a specific range, unless the variables are the beginning and end of each list. I only learn macros, it doesn't work for me. Thank you in advance. enter image description here

  • And how does the question sound? - 0xdb
  • Show an example of the location of the data. By what criteria can determine the beginning and end of the lists? - vikttur

1 answer 1

Sub SortFruits() Dim aFr(), aSort() Dim sht As Worksheet Dim lRw As Long Dim i As Long, k As Long, n As Long, m As Long Application.ScreenUpdating = False ' отключаем обновление экрана' Set sht = ActiveSheet ' нужный лист в переменную' lRw = sht.Cells(sht.Rows.Count, 1).End(xlUp).Row ' размер диапазона столбца А' aFr = sht.Range("A1:B" & lRw).Value ' данные в массив' For i = 1 To lRw ' по записям столбца А' If aFr(i, 2) = Empty Then ' к-ва нет (заголовок)' i = i + 1 For k = i To lRw ' ищем конец списка' If aFr(k, 2) = Empty Then k = k - 1: Exit For ' к-ва нет (следующий заголовок)' Next k If k > lRw Then k = lRw ' для последнего списка' ' сортировка списка' With sht.Sort .SortFields.Clear .SortFields.Add Key:=sht.Range("A" & i & ":A" & k), SortOn:=xlSortOnValues, _ Order:=xlAscending, DataOption:=xlSortNormal .SetRange sht.Range("A" & i - 1 & ":B" & k) .Header = xlYes: .Apply End With End If i = k Next i Application.ScreenUpdating = True ' включаем обновление экрана' End Sub
  • Well, really, I didn’t take into account one moment, there may be empty cells in the list, can I add something? or is it better to run a blank line delete macro? - OdUa
  • Empty - no number? It is necessary to delete, because the only thing that can be caught in the definition of the list is the lack of a number at the beginning and at the end. If there is a void inside the list, the code will count this line as the ending. Option if there are voids: enter any criteria in the lists (numbering, highlighting the title in bold ...) - vikttur
  • Although, if the entire string is empty, then you can bypass the add. checking for text in the first column. At the same time, if you do not complicate the code, the empty lines will remain and also get into the sort. Change the line inside the loop: If aFr (k, 2) = Empty And aFr (k, 1) <> Empty Then k = k - 1: Exit For - vikttur
  • The option with the text verification came up. Thanks - OdUa
  • I apologize for the intrusive, how the code will look like if the headers are bold - OdUa