помогите оптимизировать!

[kievlyanin]

For q = 2 To Sheets("ХХХ").Range("BD_syr_1").Row - 1
If Sheets("ХХХ").Cells(q, "A") <> "" Then
    For w = 8 To 300
    If Sheets("УУУ").Cells(1, w) = Sheets("ХХХ").Cells(q, "A") _
    And Sheets("УУУ").Cells(Range("BD_in_syr_end").Row + 1, w).Value = 0 Then
        Sheets("ХХХ").Cells(q, "F").Value = "удалить"
     Else
    End If
    Next w
        
End If
Next q

а потом еще один цикл удаляет строчки с "удалить" но то детали.

значит словами что тут происходит:

в таблице ХХХ берем значение в столбце А и ищем его же в таблице УУУ если находим и у этого значения в ряду (Range("BD_in_syr_end").Row + 1) таблицы УУУ значение равно нулю, то в таблице ХХХ делаем метку на удаление.


вопрос в чем - как еще можно сделать сравнение двух таблиц?? цикл долго очень - у меня только в тестовой таблице 300 строк а в реальной там и в ширину и в длинну больше будет .. долго ..

[pivas]

Если надо очень быстро, то лучше использовать функцию ВПР.

[SAS888]

Примененный Вами алгоритм, действительно будет очень долго работать. Так делать не нужно. Можно все сделать очень быстро. Только, необходимы уточнения:
1. Диапазон сравниваемых значений на листе "XXX" - это столбец "A", со строки 2 до строки Range("BD_syr_1").Row - 1. Так? Т.е. на этом листе есть именованный диапазон с именем "BD_syr_1", и мы сравниваем строки столбца "A" со 2 строки до начала этого диапазона. Это следует из Вашего кода. Подтвердите.
2. Эти значения сравниваем с ячейками 1 строки листа "YYY" с 8 по 300 столбец. И если такое значение находим, то проверяем ячейку в этом же столбце, во 2 строке именованного диапазона с именем "BD_in_syr_end", который находится на листе "YYY". По крайней мере, у Вас сделано так, что на мой взгляд, весьма странно. Тем более, что Вы пишите, что у Вас 300 строк, а сравниваете 300 столбцов. Подтвердите, или уточните задачу.
3. Что в результате нужно удалять? ячейки столбца "A" на листе "XXX" со сдвигом вверх, или строку целиком?

[kievlyanin]

Сообщение от SAS888

1. Диапазон сравниваемых значений на листе "XXX" - это столбец "A", со строки 2 до строки Range("BD_syr_1").Row - 1. Так? Т.е. на этом листе есть именованный диапазон с именем "BD_syr_1", и мы сравниваем строки столбца "A" со 2 строки до начала этого диапазона. Это следует из Вашего кода. Подтвердите.

Да.

Сообщение от SAS888

2. Эти значения сравниваем с ячейками 1 строки листа "YYY" с 8 по 300 столбец. И если такое значение находим, то проверяем ячейку в этом же столбце, во 2 строке именованного диапазона с именем "BD_in_syr_end", который находится на листе "YYY". По крайней мере, у Вас сделано так, что на мой взгляд, весьма странно. Тем более, что Вы пишите, что у Вас 300 строк, а сравниваете 300 столбцов. Подтвердите, или уточните задачу.

Уточняю - да сравниваем с ячейками 1 строки листа "YYY" с 8 по 300 столбец. И если такое значение находим, то проверяем ячейку в этом же столбце с первой строке ПОД ДИАПАЗОНОМ "BD_in_syr_end" (это одна строка) который находится на листе "YYY"

а 300 столбцов (в реале больше) это собственно база для набора второй таблицы и время от времени там надо проверять нету ли повторений -а они есть! и удалять лишние элементы, которые сейчас не используются(проверка по первой строке под диапазоном "BD_in_syr_end") и вторая таблица иногда разрастается до более чем 300 строк

Сообщение от SAS888

3. Что в результате нужно удалять? ячейки столбца "A" на листе "XXX" со сдвигом вверх, или строку целиком?

Строку целиком.

[SAS888]

Попробуйте так:

Sub Main()
    Dim i As Long, j As Long, x As Range, a, b, c
    Application.ScreenUpdating = False
    With Sheets("УУУ")
        b = .Range(.Cells(1, 8), .Cells(1, 300)).Value
        c = .Range(.Cells(Range("BD_in_syr_end").Row + 1, 8), .Cells(Range("BD_in_syr_end").Row + 1, 300)).Value
    End With
    With Sheets("ХХХ")
        a = .Range(.[A2], .Cells(.Range("BD_syr_1").Row - 1, "A"))
        For i = 1 To UBound(a, 1)
            For j = 1 To UBound(b, 2)
                If a(i, 1) = b(1, j) And c(1, j) = 0 Then If x Is Nothing Then Set x = .Rows(i + 1) Else _
                    Set x = Union(x, .Rows(i + 1))
            Next
        Next
        If Not x Is Nothing Then x.Delete
    End With
End Sub

Сразу скажу, что код не проверял, ибо самому "мастерить" файл не хочется, а Вы его не прикрепили.

[kievlyanin]

ну в целом работатет... только вопрос:

If a(i, 1) = b(1, j) And c(1, j) = 0 Then If x Is Nothing Then Set x = .Rows(i + 1) Else _
                    Set x = Union(x, .Rows(i + 1))

почему i +1 ? .. почему-то удаление идет не в диапазоне:

[A2] - Range("BD_syr_1").Row - 1


а до Range("BD_syr_1").Row


может из-за этого i +1 ??

[SAS888]

почему i +1 ?

Потому, что массив a начинается со второй строки. Т.е. a(1,1) - это ячейка "A2" (мы так формируем массив).
Для упрощения понимания, можно задать массив

a = .Range(.[A1], .Cells(.Range("BD_syr_1").Row - 1, "A"))

Тогда, соответственно, начинать проверку нужно со второго элемента:

For i = 2 To UBound(a, 1)

Теперь № эленента массива будет совпадать с № строки, которую нужно удалять. Т.е. везде, вместо i + 1 нужно записать i.

[kievlyanin]

Ок. ... а еще вот это как работает? я как-то потерялся - что х делает и что с ним делают?

If a(i, 1) = b(1, j) And c(1, j) = 0 Then If x Is Nothing Then Set x = .Rows(i + 1) Else _
                    Set x = Union(x, .Rows(i + 1))
....
          
If Not x Is Nothing Then x.Delete

[SAS888]

If x Is Nothing Then Set x = .Rows(i + 1) Else Set x = Union(x, .Rows(i + 1))

Это означает следующее: Если проверка удовлетворяет условиям, то формируем диапазон x из строк листа, которые затем будем удалять. Причем, если x еще пуст, то устанавливаем диапазон из одной строки. Если x уже не пуст - объединяем все, что есть в x и очередную строку. Затем, если x что либо содержит, то удаляем эти строки:

If Not x Is Nothing Then x.Delete

Для наглядности, можете заменить .Delete на .Select и все станет ясно.

[kievlyanin]

не .Select не надо я просто не понял оборота что если х пустое то тогда записуем туда одну строку а если нет то объединяем .. теперь конструкция понятна

и последнее - UBound.

забил в поиск и как то не совсем стыкуется инфа по функции и вот это:

For i = 1 To UBound(a, 1)
For j = 1 To UBound(b, 2)

как я понял из справки по функции "а" и "b" это массив а 1 и 2 это номер элемента записанного в массиве? .. но как тогда происходит пербор?? всего массива??