Как в эксель оставить только заполненные ячейки
Здравствуйте! искал искал не нашел, хотя наверное плохо искал. как сделать чтобы автоматически скрывались все строки если значение в столбце, например, "Е" пусто или равно 0 . и также автоматически показывались если значение в столбце >0
Здравствуйте! искал искал не нашел, хотя наверное плохо искал. как сделать чтобы автоматически скрывались все строки если значение в столбце, например, "Е" пусто или равно 0 . и также автоматически показывались если значение в столбце >0 AKSENOV048
Если строка скрыта автоматически, как в нее занесете значение, что бы автоматически показать? И что значит "автоматически"?
Если строка скрыта автоматически, как в нее занесете значение, что бы автоматически показать? И что значит "автоматически"? Michael_S
в скрытые строки изменения заносятся с помощью формы, которая добавляет данные в первую пустую строку. iLastRow = .Cells(Rows.Count, 1).End(xlUp).Row + 1
"автоматически" не знаю, может быть связать это как то с Private Sub Worksheet_SelectionChange(ByVal Target As Range)
в скрытые строки изменения заносятся с помощью формы, которая добавляет данные в первую пустую строку. iLastRow = .Cells(Rows.Count, 1).End(xlUp).Row + 1
"автоматически" не знаю, может быть связать это как то с Private Sub Worksheet_SelectionChange(ByVal Target As Range) AKSENOV048
"автоматически" не знаю, может быть связать это как то с Private Sub Worksheet_SelectionChange(ByVal Target As Range) Автор - AKSENOV048
Дата добавления - 07.09.2011 в 03:18
в скрытые строки изменения заносятся с помощью формы, которая добавляет данные в первую пустую строку. iLastRow = .Cells(Rows.Count, 1).End(xlUp).Row + 1
Это, как бы, не первая пустая, а строка, следующая за последней заполненной. А судя по вопросу, еще могут быть пустые строки в середине.
в скрытые строки изменения заносятся с помощью формы, которая добавляет данные в первую пустую строку. iLastRow = .Cells(Rows.Count, 1).End(xlUp).Row + 1
Это, как бы, не первая пустая, а строка, следующая за последней заполненной. А судя по вопросу, еще могут быть пустые строки в середине. Michael_S
в скрытые строки изменения заносятся с помощью формы, которая добавляет данные в первую пустую строку. iLastRow = .Cells(Rows.Count, 1).End(xlUp).Row + 1
Это, как бы, не первая пустая, а строка, следующая за последней заполненной. А судя по вопросу, еще могут быть пустые строки в середине. Автор - Michael_S
Дата добавления - 07.09.2011 в 07:55
Sub ert()
Dim r As Range, rng As Range
Set rng = Range("E1:E10")
Application.ScreenUpdating = False
For Each r In rng.Cells
r.EntireRow.Hidden = r.Value Next
Application.ScreenUpdating = True
End Sub
Sub ert()
Dim r As Range, rng As Range
Set rng = Range("E1:E10")
Application.ScreenUpdating = False
For Each r In rng.Cells
r.EntireRow.Hidden = r.Value Next
Application.ScreenUpdating = True
End Sub
Sub ert()
Dim r As Range, rng As Range
Set rng = Range("E1:E10")
Application.ScreenUpdating = False
For Each r In rng.Cells
r.EntireRow.Hidden = r.Value Next
Application.ScreenUpdating = True
End Sub
конечно вы правы, я просто чуть не правильно объяснил, извиняюсь. Да Вы правы строки с нулевым значением в столбце Е могут быть.
nilem, хороший макрос, осталось мне придумать как сделать чтобы он автоматически срабатывал. на событие Worksheet_SelectionChange не подошло, т.к. это маленькое зависание при каждом щелчке по ячейке. сейчас пока прилепил запуск макроса по окончанию работы формы Application.Run "ert", но все равно это не то.
да и как можно заменить rng = Range("E1:E10") на ("E3:E")
конечно вы правы, я просто чуть не правильно объяснил, извиняюсь. Да Вы правы строки с нулевым значением в столбце Е могут быть.
nilem, хороший макрос, осталось мне придумать как сделать чтобы он автоматически срабатывал. на событие Worksheet_SelectionChange не подошло, т.к. это маленькое зависание при каждом щелчке по ячейке. сейчас пока прилепил запуск макроса по окончанию работы формы Application.Run "ert", но все равно это не то.
да и как можно заменить rng = Range("E1:E10") на ("E3:E") AKSENOV048
конечно вы правы, я просто чуть не правильно объяснил, извиняюсь. Да Вы правы строки с нулевым значением в столбце Е могут быть.
nilem, хороший макрос, осталось мне придумать как сделать чтобы он автоматически срабатывал. на событие Worksheet_SelectionChange не подошло, т.к. это маленькое зависание при каждом щелчке по ячейке. сейчас пока прилепил запуск макроса по окончанию работы формы Application.Run "ert", но все равно это не то.
да и как можно заменить rng = Range("E1:E10") на ("E3:E") Автор - AKSENOV048
Дата добавления - 07.09.2011 в 11:25
Sub HideRows()
Dim x(), i&, hr As Range: Application.ScreenUpdating = 0
With Range([e1], Cells(Rows.Count, 5).End(xlUp))
x = .Value
For i = 3 To UBound(x)
If x(i, 1) = 0 Then
If hr Is Nothing Then Set hr = .Cells(i) Else Set hr = Union(hr, .Cells(i))
End If
Next i
.EntireRow.Hidden = False
End With
If Not hr Is Nothing Then hr.EntireRow.Hidden = True
Application.ScreenUpdating = 1
End Sub
Sub HideRows()
Dim x(), i&, hr As Range: Application.ScreenUpdating = 0
With Range([e1], Cells(Rows.Count, 5).End(xlUp))
x = .Value
For i = 3 To UBound(x)
If x(i, 1) = 0 Then
If hr Is Nothing Then Set hr = .Cells(i) Else Set hr = Union(hr, .Cells(i))
End If
Next i
.EntireRow.Hidden = False
End With
If Not hr Is Nothing Then hr.EntireRow.Hidden = True
Application.ScreenUpdating = 1
End Sub
Sub HideRows()
Dim x(), i&, hr As Range: Application.ScreenUpdating = 0
With Range([e1], Cells(Rows.Count, 5).End(xlUp))
x = .Value
For i = 3 To UBound(x)
If x(i, 1) = 0 Then
If hr Is Nothing Then Set hr = .Cells(i) Else Set hr = Union(hr, .Cells(i))
End If
Next i
.EntireRow.Hidden = False
End With
If Not hr Is Nothing Then hr.EntireRow.Hidden = True
Application.ScreenUpdating = 1
End Sub
Как выделить все ячейки с данными в Excel?
Когда данные остаются в прерывистых ячейках, как вы выбираете и копируете данные? Будет потрачено много времени, чтобы выбрать все прерывистые ячейки с данными одну за другой, особенно данные остаются в почти случайном порядке. Следующий хитрый способ поможет быстро выделить ячейки вызова с данными в выборках.
Выделите все ячейки с данными, за исключением формул, с помощью команды Перейти к специальной
Если нет данных, рассчитанных по формуле, Перейти к специальному Команда может помочь вам легко выделить все ячейки с данными в выборках.
Шаг 1: Выберите диапазон, с которым вы будете работать, а затем нажмите F5 , чтобы открыть Перейти к диалоговое окно.
Шаг 2: Нажмите Особый кнопка, чтобы попасть в Перейти к специальному диалоговое окно. Затем проверьте Константы вариант, см. снимок экрана:
Шаг 3: Нажмите OK, то вы увидите, что все ячейки с содержимым, за исключением ячеек формулы, выбраны.
Примечание: Таким образом невозможно выделить ячейки с формулами.
Выберите все ячейки с данными, включая формулы с кодом VBA
Если у вас есть ячейки, заполненные формулами, вы можете выбрать все ячейки с выбранными данными с помощью следующего кода VBA. Пожалуйста, сделайте так:
Шаг 1: Удерживайте Alt + F11 ключи в Excel, чтобы открыть Окно Microsoft Visual Basic для приложений.
Шаг 2: Нажмите Вставить > Модули, и вставьте следующий макрос в Окно модуля.
Код VBA: выберите все ячейки с данными, включая формулы
Шаг 3: Затем нажмите F5 нажмите клавишу для запуска этого кода, и появится окно с подсказкой, в котором вам будет предложено выбрать диапазон, в котором вы хотите выбрать ячейки данных.
Шаг 4: После выбора диапазона нажмите OK, и все ячейки данных, включая формулы в диапазоне, были выбраны.
Выберите все ячейки с данными, включая формулы, одним щелчком мыши Kutools for Excel
Если вы хотите выбрать ячейки, содержащие как значения, так и формулы, с меньшим количеством шагов, здесь я могу представить вам простой способ, Kutools for Excel, С его Выберите непустые ячейки функция, вы можете выбрать ячейки данных всего за несколько кликов.
После установки Kutools for Excel, пожалуйста, сделайте следующее :( Бесплатная загрузка Kutools для Excel сейчас )
Step1: Выберите диапазон, в котором вы хотите выбрать только ячейки данных.
Step2: Нажмите Кутулс > Выберите > Выбрать непустые ячейки, а затем были выделены сразу все ячейки с данными, см. снимок экрана:
Как скопировать и вставить только непустые ячейки в Excel?
Большинство из нас может столкнуться с этой проблемой, когда мы копируем список данных, который содержит несколько пустых ячеек, а затем вставляем их в другой столбец, пустые ячейки также будут вставлены. Это очень раздражает, когда нам не нужны пробелы и мы хотим вставить только непустые ячейки. Как в таком случае быстро и удобно копировать и вставлять в Excel только непустые ячейки? Вот несколько быстрых приемов, чтобы решить эту проблему:
Скопируйте и вставьте только непустые ячейки с помощью Kutools for Excel
Скопируйте и вставьте только непустые ячейки с помощью команды Перейти к специальной
Для Перейти к специальному , вы можете сначала выбрать все данные, а затем скопировать и вставить их в другое место.
1. Выберите свой список данных, которые вы хотите использовать.
2. Затем нажмите Главная > Найти и выбрать > Перейти к специальному, см. снимок экрана:
3. В Перейти к специальному диалоговое окно, отметьте Константы вариант, см. снимок экрана:
4. Затем нажмите OK, и в списке выбраны только ячейки значений.
5. А затем скопируйте и вставьте данные в нужное место. И были вставлены только непустые значения ячеек. Смотрите скриншот:
Внимание: Этот метод доступен только для констант, он не применяется к ячейкам формулы.
Копируйте и вставляйте только непустые ячейки с помощью функции фильтра
Фильтр Функция может помочь вам сначала отфильтровать непустые ячейки, а затем вы можете скопировать и вставить их в другие ячейки по мере необходимости.
2. Нажмите Данные > Фильтр, см. снимок экрана:
3. Затем нажмите кнопку раскрывающегося списка в правом углу ячейки в выбранном списке и снимите флажок Пробелы вариант в раскрывающемся меню. Смотрите скриншот:
4, Затем нажмите OK, все непустые ячейки были отфильтрованы, как показано на следующем снимке экрана:
5. Наконец, вы можете скопировать отфильтрованные данные и вставить их в любое нужное место.
Внимание: Таким образом вступают в силу и значения, и формулы.
Выбрать непустые ячейки сразу
Скопируйте и вставьте только непустые ячейки с формулой массива
Чтобы скопировать и вставить только непустые ячейки, вы также можете применить следующую формулу массива.
1. Помимо ваших данных, введите в пустую ячейку следующую формулу:
=LOOKUP("zzzzz",CHOOSE(,"",INDEX(A:A,SMALL(IF($A$1:$A$15<>"",ROW($A$1:$A$15)),ROWS($B$1:B1)))))
Внимание: В приведенной выше формуле A1: A15 - это список данных, который вы хотите использовать. Вы можете изменить его по своему усмотрению.
2, Затем нажмите Shift + Ctrl + Enter вместе, а затем выберите ячейку B1 и перетащите маркер заполнения в диапазон, который вы хотите содержать эту формулу, и все непустые значения ячеек были извлечены. Смотрите скриншот:
3. Поскольку они являются формулами, когда вы копируете их в другое место, вы должны копировать и вставлять их как значения.
Внимание: Эта формула доступна только для констант и не применяется к ячейкам формулы.
Скопируйте и вставьте только непустые ячейки с кодом VBA
Если вас интересует код VBA, вы можете выполнить следующий код, чтобы завершить эту задачу.
1. Удерживайте ALT + F11 ключи, и он открывает Окно Microsoft Visual Basic для приложений.
2. Нажмите Вставить > Модулии вставьте следующий код в Окно модуля.
Код VBA: копируйте и вставляйте только непустые ячейки в Excel
3, Затем нажмите F5 Нажмите клавишу для запуска этого кода, появится окно с подсказкой, напоминающее вам о выборе диапазона данных, который вы хотите использовать. Смотрите скриншот:
4, Затем нажмите OKпоявится другое окно подсказки, в котором вы сможете выбрать ячейку для ввода данных.
5. Нажмите OK чтобы завершить этот код, и только непустые значения ячеек были вставлены в указанную вами позицию.
Внимание: Этот код доступен только для констант.
Скопируйте и вставьте только непустые ячейки с помощью Kutools for Excel
Есть ли способ намного проще, чем указано выше? Конечно, Kutools for Excel's Выбрать непустую ячейкуУтилита s может помочь вам сначала выбрать непустые ячейки, а затем скопировать и вставить их.
После бесплатная установка Kutools for Excel, сделайте следующее:
1. Выберите диапазон ячеек и щелкните Кутулс > Выберите > Выбрать непустые ячейки. Смотрите скриншот:
2. Затем выбираются непустые ячейки, нажимаем Ctrl + C чтобы скопировать их, затем выберите ячейку, в которой вы хотите вывести результат, нажмите Ctrl + V для вставки выбранных непустых ячеек. Смотрите скриншот:
Как в Excel отображать только строки с определенным текстом?
В Excel функция «Фильтр» широко используется в нашей повседневной работе. И в этом случае я расскажу, как фильтровать строки с определенным текстом в Excel. Например, здесь два столбца, и я хочу показать строки, содержащие «KTE», только в столбце B, как показано ниже.
Показать строки содержат определенную строку Kutools for Excel
Показать строки содержат определенную строку с помощью функции фильтра
Чтобы отобразить строки, содержащие строку, вы можете сделать следующее:
1. Выберите используемые диапазоны и нажмите Данные > Фильтр для Фильтр функции.
2. Затем щелкните значок фильтра в столбце, по которому нужно выполнить фильтрацию, и выберите Текстовые фильтры > Комплект. Смотрите скриншот:
3. в Пользовательский автофильтр в диалоговом окне вы можете указать текст, на основе которого хотите выполнить фильтрацию. Смотрите скриншот:
4. Нажмите OK. Затем отображаются только те строки, которые содержат указанную вами текстовую строку.
Показать строки содержат определенную строку Kutools for Excel
Если вы обычно используете одни и те же критерии фильтрации для всех книг, вы можете применить Kutools for ExcelАвтора Суперфильтр функция, он может сохранить настройки фильтра для повторного использования всего несколькими щелчками мыши, без повторной настройки.
1. Выберите данные, которые вы хотите использовать, нажмите Кутулс Плюс > Суперфильтр. Смотрите скриншот:
3. Нажмите Фильтр для фильтрации диапазона.
4. Затем нажмите Сохранить текущие настройки фильтра кнопка , затем появится диалоговое окно, в котором можно дать этому сценарию имя и указать диапазон применения сценария.
5. Нажмите OK, и вы можете открыть сценарий, щелкнув раскрывающееся меню> Открыть сохраненный сценарий настроек фильтра, чтобы выбрать текущий сохраненный сценарий, или нажав Управление сценариями настройки фильтров кнопка , выберите нужную настройку фильтра и нажмите Откройте чтобы в следующий раз включить настройки фильтра.
Наконечник. Если вы хотите получить бесплатную пробную версию Суперфильтр Функция, пожалуйста, перейдите на бесплатную пробную версию Kutools for Excel сначала, а затем перейдите к применению операции в соответствии с вышеуказанными шагами.
Имеем диапазон ячеек с данными, в котором есть пустые ячейки:
Задача - удалить пустые ячейки, оставив только ячейки с информацией.
Способ 1. Грубо и быстро
- Выделяем исходный диапазон
- Жмем клавишу F5, далее кнопка Выделить(Special) . В открывшмся окне выбираем Пустые ячейки(Blanks) и жмем ОК.
Способ 2. Формула массива
Для упрощения дадим нашим рабочим диапазонам имена, используя Диспетчер Имен (Name Manager) на вкладке Формулы (Formulas) или - в Excel 2003 и старше - меню Вставка - Имя - Присвоить (Insert - Name - Define)
Диапазону B3:B10 даем имя ЕстьПустые, диапазону D3:D10 - НетПустых. Диапазоны должны быть строго одного размера, а расположены могут быть где угодно относительно друг друга.
Теперь выделим первую ячейку второго диапазона (D3) и введем в нее такую страшноватую формулу:
В английской версии это будет:
=IF(ROW()-ROW(НетПустых)+1>ROWS(ЕстьПустые)-COUNTBLANK(ЕстьПустые),"",INDIRECT(ADDRESS(SMALL((IF(ЕстьПустые<>"",ROW(ЕстьПустые),ROW()+ROWS(ЕстьПустые))),ROW()-ROW(НетПустых)+1),COLUMN(ЕстьПустые),4)))
Причем ввести ее надо как формулу массива, т.е. после вставки нажать не Enter (как обычно), а Ctrl+Shift+Enter. Теперь формулу можно скопировать вниз, используя автозаполнение (потянуть за черный крестик в правом нижнем углу ячейки) - и мы получим исходный диапазон, но без пустых ячеек:
Способ 3. Пользовательская функция на VBA
Если есть подозрение, что вам часто придется повторять процедуру удаления пустых ячеек из диапазонов, то лучше один раз добавить в стандартный набор свою функцию для удаления пустых ячеек, и пользоваться ей во всех последующих случаях.
Для этого откройте редактор Visual Basic (ALT+F11), вставьте новый пустой модуль (меню Insert - Module) и скопируйте туда текст этой функции:
Не забудьте сохранить файл и вернитесь из редактора Visual Basic в Excel. Чтобы использовать эту функцию в нашем примере:
Читайте также: