Добавление строк по условию

@xxxspeed · Регистрация: 02.03.2011

Author24 — интернет-сервис помощи студентам

Приветствую!
Есть прайс лист на 1000 позиций в excel,
нужно сделать второй лист, чтоб там отображались только те строки прайса, у которых столбец "Количество" не пустой.
То есть строки добавлялись по условию, на листе прайса она например 10, а на новом листе она окажется первой.
Подскажите решение задачи)

Название	Код товара	Цена	Кол-во	Сумма	Магазин
Кабель силовой ВВГ-Пнг(А)-LS 2*1.5	1847757	43,55			ЭТМ
Кабель силовой ВВГ-Пнг(А)-LS 3*1.5	5242955	62,04	1		ЭТМ
Кабель силовой ВВГ-нг(А)-LS 4*1.5	6751203	83,11			ЭТМ
Кабель силовой ВВГ-Пнг(А)-LS 2х2.5	7174594	69,84	1		ЭТМ

↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓

Название	Код товара	Цена	Кол-во	Сумма	Магазин
Кабель силовой ВВГ-Пнг(А)-LS 3*1.5	5242955	62,04	1	62,04	ЭТМ
Кабель силовой ВВГ-Пнг(А)-LS 2х2.5	7174594	69,84	1	69,84	ЭТМ

@Dinoxromniy · 27.06.2021, 14:33

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

@xxxspeed · 27.06.2021, 14:35 **[ТС]**

Это понятно, мне просто нужно автоматом, что б все делалось.
При изменении листа прайс, данные сразу менялись на втором листе.

Я написал формулы, чтоб на втором листе данные сразу отображались, но они отображаются в тех же строках.
Можно отсортировать и все будет ок.
Если есть вариант с авто сортировкой, при изменении таблица автоматически заново сортируется.

@Dinoxromniy · 27.06.2021, 14:46

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

Либо если есть вариант с авто сортировкой, при изменении таблица автоматически заново сортируется.

Сложно сказать - кроме вас вашу таблицу никто видел.
Если таблицу оформить через ctrl+L как умную таблицу, и она на листе единственная - то достаточно в модуль листа скопировать код:

Visual Basic

1
2
3

Private Sub Worksheet_Change(ByVal Target As Range)
Me.ListObjects.Item(1).Range.AutoFilter Field:=4, Criteria1:="<>"
End Sub

Только если вы вручную вбиваете новые данные - начинайте с 4 столбца - который "количество", иначе таблица скроет новую строчку раньше, чем вы успеете ввести все ячейки строки. С копированием проблем быть не должно.

@xxxspeed · 27.06.2021, 15:48 **[ТС]**

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

Сложно сказать - кроме вас вашу таблицу никто видел.

Краткий фрагмент таблицы я опубликовал.

С листа прайс на второй лист данные я переношу формулой =ЕСЛИ(Прайс!$D2="";"";Прайс!A2).

Код работает, я его добавил на второй лист. Единственное, нужно на втором листе сделать любое изменение, чтоб код сработал.
Как сделать, чтоб код на втором листе срабатывал при изменении первого листа?

Добавлено через 12 минут
Поправлюсь, при изменении колонки D (а лучше при изменении колонки в которой написано "Кол-во") на первом листе, срабатывал этот код на втором листе.
Потому что когда вышеуказанная формула изменяет выводимые значения код не срабатывает.

Fairuza · 27.06.2021, 16:11

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

Краткий фрагмент таблицы я опубликовал

это картинка, тут даже названий столбцов не видно, это к тому

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

при изменении колонки D

@xxxspeed · 27.06.2021, 16:23 **[ТС]**

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

это картинка, тут даже названий столбцов не видно, это к тому

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

@xxxspeed · 27.06.2021, 16:27 **[ТС]**

Вот файл, но без кода

@xxxspeed · 27.06.2021, 16:31 **[ТС]**

Вот файл.
В файле выше из за удаления кода не корректный второй лист.

@xxxspeed · 27.06.2021, 16:53 **[ТС]**

Вопрос вроде решился, разобрался.
При переключении на второй лист происходит обновление.
Изменил код вот так:

Visual Basic

1
2
3

Private Sub Worksheet_Activate()
Me.ListObjects.Item(1).Range.AutoFilter Field:=4, Criteria1:="<>"
End Sub

@Dinoxromniy · 29.06.2021, 10:08

xxxspeed, прошу прощения - руки не дошли написать.
Ваше решение оптимально, единственно - саму таблицу второго листа нужно руками растягивать (ну или изначально она должна быть много больше, чем на первом). В принципе, это можно программно делать. Насколько у вас актуально добавление строк в первую таблицу (если всего строк 1000, то во второй таблице можно сделать сразу 2000 и не париться с дополнительными решениями. Либо вставлять новые строки в табл2 каждый раз, когда добавились строки в табл1).

@xxxspeed · 29.06.2021, 21:59 **[ТС]**

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

Ваше решение оптимально

Вот еще решение которое мне подсказали

Visual Basic

Private Sub Worksheet_Activate()
Dim sh As Worksheet, sh2 As Worksheet, arr, arr2, i As Long, n As Long, x As Long, k As Long, lr As Long, lr2 As Long
Set sh = Worksheets("Прайс"): Set sh2 = Worksheets("Прайс2")
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
lr2 = sh2.Cells(Rows.Count, 1).End(xlUp).Row
arr = sh.Range("A1:G" & lr)
k = Application.WorksheetFunction.CountIf(sh.Columns(4), "<>" & "")
ReDim arr2(1 To k, 1 To 7): x = 1
For i = LBound(arr) To UBound(arr)
    If Not IsEmpty(arr(i, 4)) Then
        For n = 1 To 7
            arr2(x, n) = arr(i, n)
        Next n
        x = x + 1
    End If
Next i
If lr2 >= 4 Then sh2.Range("A4:G" & lr2).Clear
sh2.Range("A4").Resize(UBound(arr2), 7) = arr2
End Sub

Подскажите только как изменить путь к листу в

Visual Basic

1	Set sh2 = Worksheets("Прайс2")

,
и в коде

Visual Basic

1
2
3

Private Sub Worksheet_Activate()
Me.ListObjects.Item(1).Range.AutoFilter Field:=4, Criteria1:="<>"
End Sub

чтоб копировал данные в другой файл.

Но в том решении есть свой плюс. Остаются рамки в таблице

@Dinoxromniy Динохромный 1400 / 763 / 284 Регистрация: 22.12.2015 Сообщений: 2,387
	27.06.2021, 14:33	2
	xxxspeed, отфильтруйте фильтром по строке "кол-во" и скопируйте отфильтрованные значения на новый лист путем обычного выделить-копировать-вставить. 0

@xxxspeed 7 / 7 / 1 Регистрация: 02.03.2011 Сообщений: 310
	27.06.2021, 14:35 [ТС]	3
	Это понятно, мне просто нужно автоматом, что б все делалось. При изменении листа прайс, данные сразу менялись на втором листе. Я написал формулы, чтоб на втором листе данные сразу отображались, но они отображаются в тех же строках. Можно отсортировать и все будет ок. Если есть вариант с авто сортировкой, при изменении таблица автоматически заново сортируется. 0

@xxxspeed 7 / 7 / 1 Регистрация: 02.03.2011 Сообщений: 310
	27.06.2021, 15:48 [ТС]	5
	Сообщение от Dinoxromniy Сложно сказать - кроме вас вашу таблицу никто видел. Краткий фрагмент таблицы я опубликовал. С листа прайс на второй лист данные я переношу формулой =ЕСЛИ(Прайс!$D2="";"";Прайс!A2). Код работает, я его добавил на второй лист. Единственное, нужно на втором листе сделать любое изменение, чтоб код сработал. Как сделать, чтоб код на втором листе срабатывал при изменении первого листа? Добавлено через 12 минут Поправлюсь, при изменении колонки D (а лучше при изменении колонки в которой написано "Кол-во") на первом листе, срабатывал этот код на втором листе. Потому что когда вышеуказанная формула изменяет выводимые значения код не срабатывает. 0

Fairuza 6024 / 3216 / 720 Регистрация: 23.11.2010 Сообщений: 10,743
	27.06.2021, 16:11	6
	Сообщение от xxxspeed Краткий фрагмент таблицы я опубликовал это картинка, тут даже названий столбцов не видно, это к тому Сообщение от xxxspeed при изменении колонки D 0

@xxxspeed 7 / 7 / 1 Регистрация: 02.03.2011 Сообщений: 310
	27.06.2021, 16:23 [ТС]	7
	Сообщение от Fairuza это картинка, тут даже названий столбцов не видно, это к тому Верно подмечено, извиняюсь. Вот файл с добавленным кодом, с кодом файл не поддерживается сайтом 0

@Dinoxromniy Динохромный 1400 / 763 / 284 Регистрация: 22.12.2015 Сообщений: 2,387
	29.06.2021, 10:08	11
	xxxspeed, прошу прощения - руки не дошли написать. Ваше решение оптимально, единственно - саму таблицу второго листа нужно руками растягивать (ну или изначально она должна быть много больше, чем на первом). В принципе, это можно программно делать. Насколько у вас актуально добавление строк в первую таблицу (если всего строк 1000, то во второй таблице можно сделать сразу 2000 и не париться с дополнительными решениями. Либо вставлять новые строки в табл2 каждый раз, когда добавились строки в табл1). 0

	29.06.2021, 21:59