Excel вставка в отфильтрованные строки
Смотрите также: Собственно тема: скрываю происходит. по умолчанию, т.е.RMMMM у меня таких в чем причина. становлюсь в первую pasterng = Application.InputBox(«Диапазон все отфильтрованные строки протягивании все ок) — далее добавить «Запрос», Type:=8) Set же значения воГлавная формулу, цифру, т.д. написали число).Вставить только в видимые строки (или пользуюсьDemetry
с копированием выделенных: Собственно суть вопроса таблиц для обработки
оставил в покое ячейку столбца, в вставки», «Запрос», Type:=8) 
списка, то всеPelena столбец, НО тогда pasterng = Application.InputBox(«Диапазон все отфильтрованные строки > Нажимаем сочетание клавишТеперь, или нажимаем строки в фильтром, что чаще).: Надо выделить нужные ячеек проблем нет, в названии. Подробнее под сотню и макрос, начал делать который необходимо вставить ‘проверяем, чтобы они просто. Предположим, что
: А у Вас 
нарушается последовательность списка вставки», «Запрос», Type:=8) 
списка, то всеНайти и выделить «Ctrl» + «Enter».
сочетание клавиш «Ctrl»
Excel Есть диапазон ячеек ячейки, потом Ctrl-G скопированное прекрасно вставляется на всякий случай: в каждой строк способом, который описал данные, то вставляются были одинакового размера у нас есть не получилось? ЗАГОЛОВКИ ПОДПУНКТЫ с ‘проверяем, чтобы они просто. Предположим, что, а затем выберитеВ Excel ячейки
+ G, иличисла, формулы, текст я хочу чтобы — Выделить. -Только через Ctrl-V, но Есть таблица, применяю под пятьдесят тысяч) в начале темы, скопированные данные во If pasterng.SpecialCells(xlCellTypeVisible).Cells.Count <> вот такой список
Выкладывайте файл текстом описанием и
были одинакового размера у нас есть пункт можно выделить цветом клавишу F5. Выйдет можно несколькими способами. копия вставилась в видимые ячейки. Потом затирает ячейки внизу к ней фильтр,Юрий М тоже выдает ошибку. все ячейки, включая copyrng.Cells.Count Then MsgBox сделок:Гость т.п. НУЖНО СОХРАНИТЬ If pasterng.SpecialCells(xlCellTypeVisible).Cells.Count <> вот такой списокВыделение группы ячеек по поределенному условию, диалоговое окно «Переход». Когда нужно вставить видимые ячейки - обычным копипастом. (как я описал выделяю первую отфильтрованную: EgorAg, свои сообщения на разных компьютерах скрытые, если же «Диапазоны копирования и. и в нем нужно поставить: вот сам файлик ЦЕЛОСТНОСТЬ. ) ХЕЛП.
copyrng.Cells.Count Then MsgBox
сделок:. которое мы зададим Нажимаем кнопку «Выделить…». числа, формулы, текст но вставка происходитАлексей пинчук в первом посте). строку, удерживая Shift можно редактировать. В тоже пробовал уже) выделяю весь диапазон вставки разного размера!»,vbCritical фиксированную скидку вhttp://rghost.ru/38132320Serge_007 «Диапазоны копирования и. и в нем нужно поставитьВыберите параметр условным форматированием и 
И, в новом не во все во все строки: > На самом Не работает именно выделяю последнюю отфильтрованную том числе, менятьтак что, если ячеек, в которые Exit Sub End
Копирование только видимых ячеек
1000 рублей каждомув лист «все: В Excel 2007/2010 вставки разного размера!»,vbCritical фиксированную скидку втолько видимые ячейки формулой. Смотрите, как диалоговом окне «Выделение строки таблицы, можно подряд, в т.ч. деле я применил Insert (по правой строку, жму Ctrl+C, файл. В данном кому не сложно, хочу вставить эти If ‘переносим данные «Ашану». районы» в столбец по умолчанию данные Exit Sub End 1000 рублей каждому
и нажмите кнопку это сделать, в группы ячеек» ставим воспользоваться фильтром. Как и скрытые (отфильтрованные). не фильтр к кнопке мыши нет в местах пропущенных случае следовало ПОМЕНЯТЬ помогите понять в данные, то выдает из одного диапазонаФильтруем наш список Автофильтром, «Т» вставить значения вставляются только в If ‘переносим данные «Ашану».ОК статье «Закрасить ячейку галочку у слов
установить фильтр и
Как быть? диапазону, а скрыл опции Insert Copied строк бегут мураши. файл в старом
чем ошибаюсь. ошибку: «данная команда в другой только оставляя на экране
из листа «ИЗНОС» отфильтрованные строки, что из одного диапазонаФильтруем наш список Автофильтром,. по условию в «Только видимые ячейки». как фильтровать в
Владимир некоторые строки Cells). Excel английский. Перехожу на другую сообщении и проинформировать
Z не применима для в видимые ячейки только «Ашаны». Вводим
из столбца «N» бы вставить во в другой только оставляя на экранеЩелкните Excel формулой».
Нажимаем «ОК». Затем вставляем Excel, смотрите в: Вы скопируйте отфильтрованныеЭто и есть Если выделять и закладку, жму правую об этом. Зачем: В январе, однако, несвязанных диапазонов». i = 1
нужное значение вкак то так все необходимо воспользоваться в видимые ячейки только «Ашаны». ВводимКопироватьПримечание: как обычно. статье «Фильтр в строки и вставьте
Вставка в отфильтрованные строки
ответ на вопрос. копировать подряд идущие кнопку мыши на на форуме хранить лишь одна строка. kuklp For Each cell первую ячейку и вот. спецвставкой и поставить i = 1 нужное значение в(или нажмите клавишиМы стараемся какЕщё один способ Excel». Но, чтобы их в другой Высота строки (а строки, то Insert той строке куда неправильный файл?Никаких заморочек нет
Способ 1. Вставка одинаковых значений или формул
: Не раз поднималась In pasterng If протягиваем (копируем заPelena галку на «Пропускать For Each cell первую ячейку и CTRL+C). можно оперативнее обеспечивать
вызвать диалоговое окно вставить данные только лист. Думаю, проблема скрытие строки —
работает без проблем. нужно вставить скопированныеJayBhagavan — с вашими тема поиска по cell.EntireRow.Hidden = False правый нижний угол: Файл пока не
пустые ячейки». In pasterng If протягиваем (копируем заВыделите левую верхнюю ячейку вас актуальными справочными «Выделение группы ячеек». в видимые ячейки,
исчезнет. просто установка еёРешение есть с строки — но: Выделили что копировать данными — и сайту. Из-за этого Then cell.Value = ячейки) вниз: смотрела, но дляГость cell.EntireRow.Hidden = False
Способ 2. Макрос вставки любых значений
правый нижний угол области вставки и материалами на вашем На закладке «Главная» нужны свои способы,Dyka высоты в 0) промежуточным копированием нужных вместо Insert Copied (один столбец ячеек). копируется, и вставляется. модеры тиранят нерадивых copyrng.Cells(i).Value i =Умный Excel в данном копировать/вставить этот способ: Стоит 2007 . в Then cell.Value = ячейки) вниз:
нажмите кнопку языке. Эта страница в разделе «Редактирование» особенно, если много: Нет, так не будет копироваться, только строк на пустой Cells вижу просто По запросу выделилиИ давайте уточним: гостей за бестолковые i + 1 случае понимает, что действительно не пойдет. том то и copyrng.Cells(i).Value i =Умный Excel в данномВставить переведена автоматически, поэтому нажимаем на кнопку строк. пойдет. Задача вставить если копируется вся лист и последующим Insert, который вставляет куда копировать (один копировать-вставить или же названия тем. Я End If Next вы хотите ввестиЭти две таблицы дело что по i + 1
случае понимает, что(или нажмите клавиши ее текст может «Найти и выделить».Первый способ - данные в существующую строка. То есть Insert на нужную пустую строку. Если столбец ячеек). Ок.Скрытый вырезать-вставить? вот ввел в cell End Sub значения именно в ведь как-то связаны умолчанию вставляет значения End If Next
Таблица Фильтрация ВСТАВКА -СРОЧНО НУЖНА ПОМОЩЬ.
вы хотите ввести Ctrl + V). содержать неточности и
В появившемся спискеобычный талицу с отфильтрованными надо выделить не закладку, но это
сделать Ctrl-V или текст Option ExplicitНе пробовали элементарными поиск «Вставить значенияКак легко сообразить, макрос отфильтрованные ячейки и между собой, может по порядку во cell End Sub значения именно вСовет: грамматические ошибки. Для
нажимаем на функцию. строками.
конкретные ячейки, а лишний шаг. Есть Paste Special, то Sub jjj_areas_copy() Dim горячими клавишами воспользоваться, в отфильтрованные ячейки» запрашивает у пользователя делает то, что формулой попробовать найти все скрытые строки
Как легко сообразить, макрос отфильтрованные ячейки и Чтобы скопировать выделенный фрагмент нас важно, чтобы «Выделение группы ячеек».Возьмем такую таблицу.Dyka строки полностью. ли прямой путь строки вставляются, но
rngSrc As Range да и оформить и сразу по очереди два нужно: нужное значение? в столбце. а не запрашивает у пользователя делает то, что на другой лист эта статья была Чтобы Таблица будет одна
: С макросами плохоAleksFin с непосредственным Insert затирают строки расположенные
Dim rngTrgt As данные таблицей/списком?!
EgorAg диапазона — копированияТакой способ подойдет и
Tviga в нужные отфильтрованные, по очереди два нужно:
или в другую вам полезна. Просимзаполнить видимые ячейки в для всех примеров. знаком, но не: Здравствуйте! куда нужно? ниже. Явно должно Range Dim iEgorAg: спасибо за совет. и вставки. Затем для ввода значений
: вобщем так. побывал и спецвставкой диапазона — копированияТакой способ подойдет и книгу, щелкните вкладку
вас уделить пару выделенных столбцах ExcelУберем фильтром все цифры подскажете основной ходПомогите пжл, в_Boroda_
быть просто, но As Long, j: извиняюсь, я там
видел. не работает.
проверяет, чтобы их и для ввода
не совсем понятно
с галкой «Пропускать и вставки. Затем для ввода значений другого листа или секунд и сообщить,
, нажимаем сочетание клавиш 2 из таблицы.
мыслей? Метод Paste таблице есть группированные: А, да это что то туплю. As Long If
лишний столбец с пробовал уже много размеры совпадали, т.к. формул. Например, если как завязаны между
пустые ячейки». НЕ проверяет, чтобы их
и для ввода выберите другую книгу помогла ли она «Ctrl» + D. В оставшиеся видимые использовать по 1 строки с данными, скорее всего у Помогите пожалуйста. TypeName(Selection) = «Range» названием «месяц» добавил, раз ранее. дает
разница в размерностях
скидка для «Ашанов»
Вставка в отфильтрованные строки
собой листы. Нет ВЫХОДИТ. размеры совпадали, т.к. формул. Например, если и выделите левую вам, с помощью И все выделенные ячейки поставим цифру ячейке с провыеркой хочу скопировать только Вас книги открытыkrosav4ig Then Set rngSrc где месяца перечислены ошибку. вызовет впоследствии ошибку не фиксированная, а никаких одинаковых данных.Гость разница в размерностях скидка для «Ашанов» верхнюю ячейку области
Способ 1. Вставка одинаковых значений или формул
кнопок внизу страницы. столбцы заполнятся данными 600. В ячейку на скрытость строк? итоги по сгруппированным в разных Excelях.: Попробуйте перед копированием = Selection.Resize(, 1).SpecialCells(xlCellTypeVisible) (сейчас файл подправлен).
по этой причине при вставке. Затем составляет 10% от Куча повторяющихся значений.
: Короче никто не вызовет впоследствии ошибку не фиксированная, а вставки. Для удобства также или формулой, как В2 ставим число или как то
строкам и вставить Попробуйте закрыть вторую выделить только видимые On Error Resume а так я и создал тему макрос перебирает все
суммы сделки, то Кодов нет. Из знает и никто при вставке. Затем составляет 10% отПримечание: приводим ссылку на в первой ячейке. 600, затем копируем по другому? их рядом, но книгу и открыть
Способ 2. Макрос вставки любых значений
ячейки (F5>Alt+ВЫ>enter) Next Set rngTrgt фильтрую по столбцу еще раз. более ячейки в диапазоне в первую отфильтрованную строку того что было не поможет. макрос перебирает все суммы сделки, то При копировании значения последовательно оригинал (на английском В нашем примере, его вниз поTimSha вставить в те ее из первойRMMMM
= Application.InputBox(«», , «вид затрат», выбираю того, даже обращался вставки и переносит можно ввести не предложено могу толькоSerge_007 ячейки в диапазоне в первую отфильтрованную строку вставляются в строки языке) . мы написали число800 столбцу (тянем за: А в чем же ячейке по — Файл -: krosav4ig, по F5 , , , «себестоимость», и у к человеку, который туда данные из константу (1000), а так. Вставить пару: Я уже ответил. вставки и переносит можно ввести не и столбцы. ЕслиЕсли некоторые ячеек, строк в ячейку D2 правый нижний угол «изыск» такого подхода? строкам с которых Открыть
у меня Go , , 8).Resize(, меня в столбце отсылал с этого диапазона копирования, если формулу (=C2*10%) и также скопировать вниз. столбцов. И формулу.Почему у Вас туда данные из константу (1000), а область вставки содержит и столбцов на столбца D. Немного ячейки В2). Значения Может по-другому следует и скопировал.RMMMM
Вставить скопированные значения в отфильтрованные ячейки
To открывается. Excel 1).SpecialCells(xlCellTypeVisible) If Not «февраль» заполнены 2
форума кого-то ранее строка видима (т.е.Другое дело, если вам А больше не не так - диапазона копирования, если формулу (=C2*10%) и также скопировать вниз.
скрытые строки или листе не отображается, не по теме, скопировались только в взглянуть на проблемуПо факту получается,: 2010. А что rngTrgt Is Nothing
строки: №2 и к подобному макросу. не отфильтрована). необходимо вставить в знаю как. надо разбираться локально строка видима (т.е.Другое дело, если вам столбцы, возможно, потребуется у вас есть но хочется сказать видимые ячейки. Точно и найти другое копирую выделением при_Boroda_ такое Alt-ВЫ? Then If rngSrc.Cells.Count №7. я их но в ответEgorAg отфильтрованные ячейки неВроде все сошлось.
Pelena не отфильтрована). необходимо вставить в отобразить их, чтобы возможность копирования всех здесь. Сочетание клавиш также можно вставлять решение? сгруппированном состоянии ячейки,, действительно были открытыPelena
= rngTrgt.Cells.Count And копирую и вставляю
— «тишина»!: Добрый день, уважаемые одинаковые значения илиhttp://zalil.ru/33260588
: Я так понимаюГость отфильтрованные ячейки не увидеть все скопированные ячеек, или только «Ctrl» + R и формулы. МыGuest потом вставляю в две книги, правда
: F5 — Выделить rngSrc.Areas.Count = rngTrgt.Areas.Count в столбец «январь».
Z форумчане! формулы, а разные,Весьма распространенная ситуация, вопрос новые данные вставляются: Уважаемые знающие люди. помогите
одинаковые значения или данные.
видимые ячейки. По – копирует данные в ячейке С2: Основной ход мысли нужные мне, и в одном экселе. — Только видимые Then For j копирую выделив 2: Значит вы. ошибаетесьТема эта поднималась да еще и про которую мне с помощью заполнения? плиз.
формулы, а разные,Весьма распространенная ситуация, вопрос умолчанию Excel копирует по строке Excel пишем такую формулу. — если видима эксель вставляет их
На всякий случай ячейки — ОК = 1 To строчки и вставляю
где-то, в чем-то. уже не раз, брать их из другого
задают почти наТогда нужно сделатьСуть есть список да еще и
про которую мне скрытых или отфильтрованных (в нашем примере
=А2*10 или если высота подряд, а не все закрыл, открыл
— дальше Копировать/Вставить rngTrgt.Areas.Count If rngSrc.Areas(j).Rows.Count выделив в столбцеМо быть - но на практике диапазона. Тогда придется использовать каждом тренинге. Есть так: отфильтровать столбец, из около 2000 брать их из другого задают почти на ячейки не только – строка 2,Получилось так. строки не = в нужные мне только одну нужную_Boroda_ <> rngTrgt.Areas(j).Rows.Count Or январь 2 строчки. «чтобы их размеры реализовать советы не несложный макрос. Нажмите таблица, в которой выделить диапазон ячеек, строк с огловлениями диапазона. Тогда придется использовать
каждом тренинге. Есть видимые ячейки. Если копировали число 800Отменим фильтр. Получилась такая 0 то вставалять. строки. книгу — все: Еще можно Альт+ТочкасЗапятой
_ rngSrc.Areas(j).Columns.Count <> соответственно, если делаю совпадали, т.к. разница получается. Поэтому обращаюсь сочетание клавиш Фильтром ( которые хотим заполнить, пустыми строками . несложный макрос. Нажмите таблица, в которой
это не является из ячейки D2). таблица.DykaPelena то же самое,На разных компьютерах rngTrgt.Areas(j).Columns.Count Then Exit это без применения в размерностях вызовет к вам за
Alt+F11Данные — Фильтр затем в диалоговом и 15 столбцами сочетание клавиш Фильтром ( вас не устраивают, Получилось так.Формула и цифры вставились: The_Prist большущее спасибо. : Возможно, это ищете копируются выделенные строки по-разному — иногда Sub Next j макроса, то вставляется впоследствии ошибку при помощью., в открывшемся окне) отобраны несколько строк. окне ВЫДЕЛЕНИЕ ГРУППЫфильтрую по допустимAlt+F11Данные — Фильтр выполните действия, описанныеТретий способ. только в отфильтрованныеочень пригодиться в Вставка в отфильтрованные без проблем, Paste работает Альт+Ж, иногда For j = без учета «фильтрации», вставке.»Проблема тривиальная: есть Visual Basic вставьте Задача — вставить ЯЧЕЕК поставить переключатель 5 столбцу, получаю, в открывшемся окне) отобраны несколько строк. в этой статье,В новом столбце
строки. работе эта вещь. строки без проблем, Insert
Альт+Контрл+4 1 To rngTrgt.Areas.Count а если сps Правила пп
массив, состоящий из
Как вставить скопированные по фильтру строки? (Формулы/Formulas)
новый пустой модуль какие-либо нужные нам ТОЛЬКО ВИДИМЫЕ ЯЧЕЙКИ, список из 560 Visual Basic вставьте Задача — вставить чтобы скопировать только (в нашем примереВторой способ.Правда горячие клавишиили ещё просто вставляет пустуюkrosav4ig For i = макросом, то выдает 2.2, 2.3. нескольких столбцов, произвожу через меню значения именно в ввести нужное значение строк. в соседний новый пустой модуль какие-либо нужные нам видимые ячейки. Например – столбец Е)Также отфильтруем данные. вроде как неhttp://www.excel-vba.ru/chto-um. achejki строку, Insert Copied: Вы нажмите F5
1 To rngTrgt.Areas(j).Cells.Count ошибку.EgorAg фильтрацию и выделенные
Insert — Module видимые отфильтрованные строки, и нажать +. столбец ПЫТАЮСЬ вставить через меню значения именно в
вы можете скопировать выделяем ячейки. Нажимаем В первой ячейке работают (по ctrl+wMichael_S
Cells отсутствует. Возможно и посмотрите, какие
rngSrc.Areas(j).Cells(i).Copy rngTrgt.Areas(j).Cells(i) Nextгорячими клавишами пользовался,: для примера, сделал данные из одного
и скопируйте туда пропуская при этом В этом случае новые данные . Insert — Module видимые отфильтрованные строки, только итоговых данных клавишу F5. Выйдет
пишем число, формулу, документ закрывается), но: По шагам, на данный функционал просто
буковки Alt при i Next j тоже нашел на таблицу, где нужно столбца необходимо вставить этот код: скрытые. Обычное копирование-вставка заполнятся только видимые и получается чтои скопируйте туда пропуская при этом из структурированных листа. диалоговое окно «Переход». текст, т.д. Теперь, я макросы на вашем примере: не предусмотрен в нажатом подчеркнуты, вот End If End просторах инета, но скопировать 2 строки
в другой столбец,Sub PasteToVisible() Dim при этом не ячейки. вставляются данные во этот код: скрытые. Обычное копирование-вставкаСделайте следующее: Нажимаем кнопку «Выделить…». если строк тысячи,
форму-кнопку повесил ивыделяем ячейки в столбце экселе, хотя конечно их и нужно If End If результата нет, сейчас из февраля и синхронно, строка в copyrng As Range, сработает, т.к. данные вставятся
Гость все по порядкуSub PasteToVisible() Dim при этом неВыделите ячейки, которые нужно И, в новом то выделяем ячейки тогда все получилось. Е:Е это как то жать при зажатом End Sub уже не помню, вставить в 2 строку. pasterng As Range не только в: Как вариант могу скрытие ячейки, а copyrng As Range, сработает, т.к. данные вставятся скопировать подробнее, читайте
Excel: Когда копирую отфильтрованные ячейки и вставляю их на другой лист, то вставляются также и скрытые фильтром ячейки
диалоговом окне «Выделение
так: нажимаем клавишиDykaЖмем Ctrl+G
странно. AltEgorAg какую именно давал строки января, вроде
Пытался реализовать это Dim cell As видимые, но и скинуть вам файл, не в те pasterng As Range
не только в статью выберите ячеек, группы ячеек», ставим «Ctrl» + «Shift»: Только, наверное, неВыделить
ячейки. Как исправить это?В: Юрий М, извиняюсь. ошибку, но не здесь уже сложно путем выделения отфильтрованных Range, i As в скрытые ячейки. попробуете, если все которые я фильтранул. Dim cell As видимые, но и
Копирование и вставка через не затрагивая заполненные строки (Формулы/Formulas)
диапазонов, строк и галочку у слов
+ кнопку (стрелочку) обязательно делать проверкуТолько видимыеМне надо чтобыыделить > Видим я новичок на вставлял. в такой таблице ячеек, последующего вызова Long ‘запрашиваем у Давайте посмотрим, как
получится буду благодарен. ВОПРОС как вставить Range, i As в скрытые ячейки. столбцов на листе. «Только видимые ячейки». вниз (или кнопку на количество выделенныхв строке формул =C11
не копировались ячейкиы форуме. сейчас подправлю.с переоформлением таблицы
в подсчетах ошибиться,
меню «выделение группы
пользователя по очереди можно обойти этуГость
- в те которые Long ‘запрашиваем у
- Давайте посмотрим, как
- Совет:
- Нажимаем «ОК». Теперь,
- вверх, зависит от
- строк на втором
скрытые фильтром.е
Как при вставке ячеек игнорировать скрытые строки?
EgorAg сложно. просто я и мне выдает ячеек» и выбора диапазоны копирования и проблему.: новые данные вставляются отфильтрованы. пользователя по очереди можно обойти эту Чтобы отменить выделение ячеек, не отменяя выделения,
шаге, вероятно, чтобыВсеПомню раньше, когдаRMMMM: JayBhagavan, большое спасибо. упростил ее по
ошибку: диапазоны копирования «выделить только выбранные вставки Set copyrngЕсли вам нужно вставить не с помощью(можно скопировать отфильтрофанные
диапазоны копирования и проблему. щелкните любую ячейку в первой ячейке выделить ячейки – этого избежать надоAleksFin то, получалось нормально: Не получается. По все работает очень
максиму, чтобы ошибку и вставки разного ячейки», соответственно их = Application.InputBox(«Диапазон копирования», одни и те заполнения, а копируются данные на новый
вставки Set copyrngЕсли вам нужно вставить на листе. столбца (у нас ниже или выше сделать подсчет скопированных
: Отлично, спасибо копировать, но не
моему Excel 2010 прекрасно.
найти. а так размера. не пойму копирования и если «Запрос», Type:=8) Set же значения во с нового листа. (при лист «вставить значения»
Как скопировать и вставить только видимые ячейки в Microsoft Excel
Когда дело доходит до программ для выполнения задач с электронными таблицами, нельзя отрицать, что Microsoft Excel является самым популярным и используемым инструментом не только в нашей стране, но и во всем мире. Одно из действий, которое мы чаще всего выполняем при его использовании, — это копирование и вставка, что очень легко сделать. Однако, когда дело доходит до утечки данных, копирование и вставка этих данных не всегда так просто, как можно было бы ожидать.
В том случае, если мы когда-нибудь пытались вставить что-то в отфильтрованную таблицу, мы наверняка убедились, что это не так просто, как кажется. При копировании таблиц мы, вероятно, время от времени копируем скрытые строки или столбцы. Это может ввести в заблуждение, особенно если мы используем Excel для подсчета сумм собранных данных. В других случаях мы просто хотим скопировать только видимые строки и оставить скрытые.
Вот почему мы увидим, как мы можем копировать данные из отфильтрованного набора данных и как вставлять их в отфильтрованный столбец, опуская скрытые ячейки.
Скопируйте видимые ячейки в Excel
Please enable JavaScript
В случае, если некоторые ячейки, строки или столбцы электронной таблицы не отображаются, у нас есть возможность скопировать все ячейки или только видимые. По умолчанию программа Microsoft для работы с электронными таблицами копирует скрытые или отфильтрованные ячейки в дополнение к тем, которые видны. Если мы этого не хотим, мы должны выполнить шаги, описанные ниже, чтобы скопировать только видимые ячейки.
Для этого мы должны выбрать ячейки, которые мы хотим скопировать. Затем переходим на вкладку «Пуск». Здесь мы нажимаем «Найти и выбрать», который мы находим с правой стороны, представленный значком увеличительного стекла. Появится меню, в котором мы выберем «Перейти к особому» опцию.
Это приведет к появлению нового всплывающего окна, в котором мы можем выбирать между несколькими вариантами, которые оно нам позволяет: ячейки с комментариями, константы, формулы, пробелы, ячейки с проверкой данных и т. д. Здесь нас интересует «Только видимые ячейки» вариант, поэтому мы нажимаем на него, чтобы выбрать и нажимаем «ОК».
Далее, с выбранными ячейками, мы нажимаем, для чего мы можем использовать сочетание клавиш Ctrl + C или щелкните правой кнопкой мыши и выберите Копировать. Мы также можем щелкнуть непосредственно значок «Копировать» (значок с двумя страницами), который мы находим в разделе «Буфер обмена» на вкладке «Главная».
Теперь нам нужно только перейти в поле, куда мы хотим вставить скопированные ячейки, и использовать действие «Вставить». Для этого мы можем использовать сочетание клавиш Ctrl + V , щелкните правой кнопкой мыши и выберите «Вставить» или щелкните параметр «Вставить», который находится внутри буфера обмена на вкладке «Главная».
Копировать из отфильтрованного столбца без скрытых ячеек
Возьмем в качестве примера, что у нас есть таблица с расписанием, в столбцах которой указаны дни недели, а в строках — часы. Из него мы хотим скопировать все строки для СРЕДА, в которых есть МАТЕМАТИКА. Для этого мы должны применить фильтр.
Сначала мы выберем всю таблицу и перейдем на вкладку «Данные». Здесь мы выбираем «Фильтр» кнопку, которую мы находим в группе «Сортировка и фильтрация».
Это приведет к тому, что в каждой ячейке строки заголовка появятся маленькие стрелки, которые помогут нам фильтровать, так как это будет необходимо только нажать на любую стрелку, чтобы выбрать фильтр для соответствующего столбца. В этом случае, поскольку мы хотим отфильтровать строки МАТЕМАТИКА в разделе СРЕДА, мы выбираем стрелку в этом заголовке.
Теперь появляется всплывающее окно, где мы снимаем флажок «Выбрать все» и оставьте отмеченным только поле МАТЕМАТИКА. Наконец, мы нажимаем «Принять», и мы увидим только те строки, в которых МАТЕМАТИКА появляется в расписании.
Далее, копирование из отфильтрованной таблицы проще , потому что когда мы копируем из отфильтрованного столбца или таблицы, Excel автоматически копирует только видимые строки. Поэтому нам нужно только выбрать видимые строки, которые мы хотим скопировать, и нажать любой из методов копирования, например Ctrl + C. Мы выбираем первую ячейку, куда мы хотим вставить скопированные ячейки, и нажимаем любой из методов вставки, например, Ctrl + V, и ячейки будут вставлены.
Также в Google Таблицах
Приложение Google Spreadsheets представляет собой программу, похожую на Excel, которая имеет много общих функций и работает в Интернете. Из Таблиц мы также можем копировать и вставлять только видимые ячейки. Для этого мы должны получить доступ к своему веб-сайту и открываем проект, над которым мы работаем и хотим выполнить эту функцию.
Для этого зажимаем клавишу Ctrl и кликаем по всем видимым ячейкам, которые хотим скопировать. Как только все выбрано, мы копируем их с помощью сочетания клавиш «Ctrl + C» или с помощью правой кнопки мыши. Позже мы вставляем строки в другое место или в другой файл.
Копировать отфильтрованный столбец
Если мы хотим скопировать отфильтрованный столбец без отображения скрытых ячеек, мы должны выполнить следующие шаги. Предположим, у нас есть таблица с расписанием, и мы хотим скопировать все строки из СРЕДА, в которых есть МАТЕМАТИКА. Для этого мы должны применить фильтр. Сначала мы выделим всю таблицу и перейдем на вкладку «Данные». Здесь откроется меню, где мы нажимаем на «Создать фильтр» .
Это приведет к появлению стрелок в заголовке каждого столбца, которые помогут нам создать фильтр. Нажимаем на нужную стрелку, в данном случае СРЕДА. Мы прокручиваем вниз и снимаем все опции, кроме MATH, и нажимаем «ОК». Это приведет к тому, что мы увидим только те строки, в которых МАТЕМАТИКА появляется в расписании СРЕДА.
Как и в случае покупки, мы отфильтровали все нужные ячейки в столбце, так что теперь мы можем легко копировать и вставлять видимые ячейки.
Теперь нам нужно только выбрать видимые строки, которые мы хотим скопировать, и использовать сочетание клавиш «Ctrl + C». Затем мы выбираем первую ячейку, куда мы хотим вставить скопированные ячейки, и нажимаем кнопку «Вставить» с помощью сочетания клавиш «Ctrl + V», и ячейки будут вставлены.
Как вставить скопированные ячейки только в видимые/отфильтрованные ячейки
Ни для кого не секрет, что Excel позволяет выделять только видимые строки. Например, если некоторые из них скрыты или к ним применен фильтр.
если кто-то не знает, как это сделать: выделяем диапазон — Alt+;(для английской раскладки);Alt+ж(для русской). Подробнее можно почитать в этой статье
Если после выделения только видимых ячеек их скопировать, то скопируются они как положено. Но при попытке вставить скопированное в отфильтрованный диапазон(либо содержащий скрытые строки) — то результат вставки будет не совсем такой, как Вы ожидали. Данные будут вставлены даже в скрытые строки. Либо как вариант получим ошибку «Данная команда не применима к несвязанному диапазону».
Копируем единый диапазон ячеек и вставляем только в видимые
Чтобы данные вставлялись только в видимые ячейки, можно применить такой макрос:
Option Explicit Dim rCopyRange As Range ‘Этим макросом копируем данные Sub My_Copy() If Selection.Count > 1 Then Set rCopyRange = Selection.SpecialCells(xlVisible) Else: Set rCopyRange = ActiveCell End If End Sub ‘Этим макросом вставляем данные, начиная с выделенной ячейки Sub My_Paste() If rCopyRange Is Nothing Then Exit Sub If rCopyRange.Areas.Count > 1 Then MsgBox "Вставляемый диапазон не должен содержать более одной области!", vbCritical, "Неверный диапазон": Exit Sub Dim rCell As Range, li As Long, le As Long, lCount As Long, iCol As Integer, iCalculation As Integer Application.ScreenUpdating = False iCalculation = Application.Calculation: Application.Calculation = -4135 For iCol = 1 To rCopyRange.Columns.Count li = 0: lCount = 0: le = iCol — 1 For Each rCell In rCopyRange.Columns(iCol).Cells Do If ActiveCell.Offset(li, le).EntireColumn.Hidden = False And _ ActiveCell.Offset(li, le).EntireRow.Hidden = False Then rCell.Copy ActiveCell.Offset(li, le) lCount = lCount + 1 End If li = li + 1 Loop While lCount >= rCell.Row — rCopyRange.Cells(1).Row Next rCell Next iCol Application.ScreenUpdating = True: Application.Calculation = iCalculation End Sub
Как использовать: Для начала надо убедиться, что разрешены макросы и при необходимости включить их: почему не работает макрос. Затем копируем код выше, из Excel переходим в редактор VBA( Alt + F11 ) —Insert —Module. Вставляем туда скопированный код. Теперь код можно вызывать нажатием клавиш Alt + F8 -выделяем имя макроса —Выполнить (Run) .
Для полноты картины, данные макросы лучше назначить на горячие клавиши(в приведенных ниже кодах это делается автоматически при открытии книги с кодом). Для этого приведенные ниже коды необходимо просто скопировать в модуль ЭтаКнига(ThisWorkbook):
Option Explicit ‘Отменяем назначение горячих клавиш перед закрытием книги Private Sub Workbook_BeforeClose(Cancel As Boolean) Application.OnKey "^q": Application.OnKey "^w" End Sub ‘Назначаем горячие клавиши при открытии книги Private Sub Workbook_Open() Application.OnKey "^q", "My_Copy": Application.OnKey "^w", "My_Paste" End Sub
Теперь можно скопировать нужный диапазон нажатием клавиш Ctrl + q , а вставить его в отфильтрованный — Ctrl + w .
Если необходимо переносить только значения (т.е. если в ячейке будут формулы, то в итоге будет перенесен результат вычисления этой формулы), надо заменить строку в коде:
rCell.Copy ActiveCell.Offset(li, le)
ActiveCell.Offset(li, le) = rCell.Value
Копируем только видимые ячейки и вставляем только в видимые
По многочисленным просьбам доработал код. Теперь возможно копировать любые диапазоны: со скрытыми строками, скрытыми столбцами и вставлять скопированные ячейки также в любые диапазоны: со скрытыми строками, скрытыми столбцами. Работает совершенно так же, как и предыдущий: нажатием клавиш Ctrl + q копируем нужный диапазон(со скрытыми/отфильтрованными строками и столбцами или не скрытыми), а вставляем сочетанием клавиш Ctrl + w . Вставка производится так же в скрытые/отфильтрованные строки и столбцы или без скрытых.
Если в копируемом диапазоне присутствуют формулы, то во избежание смещения ссылок можно копировать только значения ячеек — т.е. при вставке значений будут вставлены не формулы, а результат их вычисления. Или если необходимо сохранить форматы ячеек, в которые происходит вставка — будут скопированы и вставлены только значения ячеек. Для этого надо заменить строку в коде(в файле ниже):
rCell.Copy rResCell.Offset(lr, lc)
rResCell.Offset(lr, lc) = rCell.Value
В файле ниже обе эти строки присутствуют, Вам надо лишь оставить ту, которая больше подходит под Ваши задачи.
Статья помогла? Поделись ссылкой с друзьями!
Видеоуроки
Поиск по меткам
А как изменить данный макрос, что бы он не копировал видимый диапазон, а вырезал его?
Пробовал заменить все «Copy» на «Cut», но увы.
Александр, увы, но в данном случае простой замены мало. Cut работает чуть иначе. Поэтому Вам надо в коде сначала оставить все как есть, а в конце переноса данных(после Next iCol) добавить что-то вроде rCopyRange.Delete
копирование и вставка значений несвязных ячеек (диапазонов)
‘Выделяете нужные ячейки на листе1, запускаете макрос — выбираете КУДА(destination) копировать — жмете ОК.
Public Sub ValuesOfDisconnectedCells()
Dim n&, c As Range, r As Range
ReDim a(1 To Selection.Cells.Count)
For Each c In Selection
n = n + 1: a(n) = c.Value
Next
Set r = Application.InputBox(«Select destination cells», Type:=8)
If r.Cells.Count = n Then
n = 0
For Each c In r.Cells
n = n + 1:c = a(n)
Next
End If
End Sub
Можно приспособить данный макрос к работе в защищенных листах? Область вставки не заблокирована.
Перед вставкой снимайте защиту(можно кодом — Selection.Parent.Unprotect «password» ). Иначе не получится, т.к. используется метод SpecialCells, который невозможно использовать при активной защите листа.
Это просто пеееееееесня!
СПАСИБОЧКИ ВАМ, ДМИТРИЙ, БОЛЬШУЩЕЕ. Два дня сидела и не могла понять — как сделать так, чтобы копировались только значения ячеек, а не формулы — все комментарии прочла, не понимала — а всё элементарно — НУЖНО ОСТАВИТЬ ТОЛЬКО ТУ СТРОЧКУ, КОТОРАЯ ВСТАВЛЯЕТ ЗНАЧЕНИЯ. Господи, всего — то пару слов в статье, в самом конце — и решение проблемы вселенского масштаба.
Спасибо Вам, большое! Успехов Вам и процветания Вам и Вашим близким!
Храни Вас Бог!
Добрый день! Подскажите, как поменять данный макрос, чтобы скопированные ячейки вставлялись значениями?
nekamon, для этого достаточно дочитать внимательно до конца статью. Там этот случай как раз описан с указанием того, какую строку кода на что заменить.
Вставка в отфильтрованные строки
Весьма распространенная ситуация, вопрос про которую мне задают почти на каждом тренинге. Есть таблица, в которой Фильтром (Данные — Фильтр) отобраны несколько строк. Задача — вставить какие-либо нужные нам значения именно в видимые отфильтрованные строки, пропуская при этом скрытые. Обычное копирование-вставка при этом не сработает, т.к. данные вставятся не только в видимые, но и в скрытые ячейки. Давайте посмотрим, как можно обойти эту проблему.
Способ 1. Вставка одинаковых значений или формул
Если вам нужно вставить одни и те же значения во все отфильтрованные строки списка, то все просто. Предположим, что у нас есть вот такой список сделок:
. и в нем нужно поставить фиксированную скидку в 1000 рублей каждому "Ашану".
Фильтруем наш список Автофильтром, оставляя на экране только "Ашаны". Вводим нужное значение в первую ячейку и протягиваем (копируем за правый нижний угол ячейки) вниз:
Умный Excel в данном случае понимает, что вы хотите ввести значения именно в отфильтрованные ячейки и делает то, что нужно:
Такой способ подойдет и для ввода значений и для ввода формул. Например, если скидка для "Ашанов" не фиксированная, а составляет 10% от суммы сделки, то в первую отфильтрованную строку можно ввести не константу (1000), а формулу (=C2*10%) и также скопировать вниз.
Способ 2. Макрос вставки любых значений
Другое дело, если вам необходимо вставить в отфильтрованные ячейки не одинаковые значения или формулы, а разные, да еще и брать их из другого диапазона. Тогда придется использовать несложный макрос. Нажмите сочетание клавиш Alt+F11, в открывшемся окне Visual Basic вставьте новый пустой модуль через меню Insert — Module и скопируйте туда этот код:
Как легко сообразить, макрос запрашивает у пользователя по очереди два диапазона — копирования и вставки. Затем проверяет, чтобы их размеры совпадали, т.к. разница в размерностях вызовет впоследствии ошибку при вставке. Затем макрос перебирает все ячейки в диапазоне вставки и переносит туда данные из диапазона копирования, если строка видима (т.е. не отфильтрована).
Ссылки по теме
Можно еще немного проще:
1. отфильтровать
2. выделить весь столбец — F5 — Выделить — Только видимые
3. не снимая выделения, ввести формулу в первую ячейку и нажать Ctrl+Enter
Спасибо за полезное уточнение
Позволю себе выложить здесь ссылку «в тему»(надеюсь Николай не будет против): Как вставить скопированные ячейки только в видимые/отфильтрованные ячейки
Достаточно давно написал код, который выложен в статье. Можно скопировать только видимые ячейки и вставить так же только в видимые. Могут быть скрыты как строки, так и столбцы. В общем совершенно без разницы что и как скрыто, вставлено будет в то, что видно
Я только «за», спасибо — ценная штука!
Не за что, Самвел
Спасибо Вам большое! Ваши «ПРИЕМЫ» спасает огромное количество рабочих часов!
Повесила макрос на горячую клавишу и красота
Вопрос.
1. Как провести копирование данных из одного фильтрованного диапазона в аналогичных по размерам другой?
2. Как сохранить макрос так, чтоб его можно было использовать в других открытых\созданных файлах, чтоб он стал «постоянным»?
1. Вот так:
Sub PasteToVisible()
Dim copyrng As Range, pasterng As Range
Dim cell As Range, i As Long
‘запрашиваем у пользователя по очереди диапазоны копирования и вставки
Set copyrng = Application.InputBox(«Диапазон копирования», «Запрос», Type:=8)
Set pasterng = Application.InputBox(«Диапазон вставки», «Запрос», Type:=8)
‘проверяем, чтобы они были одинакового размера
If pasterng.Cells.Cells.Count <> copyrng.Cells.Count Then
MsgBox «Диапазоны копирования и вставки разного размера!»,vbCritical
Exit Sub
End If
‘переносим данные из одного диапазона в другой только в видимые ячейки
For Each cell In pasterng
If cell.EntireRow.Hidden = False Then
cell.Value = Cells(cell.Row, copyrng.Column).Value
End If
Next cell
End Sub
Николай Павлов, большое спасибо за макрос! Я, хоть и сталкивалась с excel, но с макросами дела не имела. А тут понадобилось в большущую таблицу выгрузки сайта, фильтруя ее значения, вставлять не цифры и формулы, а текст, уникальные названия товаров. Копировать значения ячеек по одному совершенно нереально, долго и запутаешься. А с макросом (диапазон вставки и копирования были на разных листах одной книги) все получилось, пока с одним значением фильтра. Но встретились в конце такая закавыка.
Фильтр сняла, вернее, поставила галочки у всех значений, как у вас в видео. Здесь было все нормально, все вернулось к прежнему виду, правда, я не проверила всю портянку. Только вот почему-то после нажатия кнопки сохранить мне выпало окошко — как будто ошибка. Там написано:
«выгрузка.csv» может содержать возможности несовместимые с форматом «CSV( разделители — запятые)». Сохранить книгу в этом формате?
Чтобы сохранить этот формат, удалив все несовместимые возможности, нажмите кнопку Да.
Чтобы сохранить все возможности, нажмите кнопку нет и сохраните файл в формате последней версии excel.
Чтобы узнать, какие возможности могут быть потеряны, нажмите кнопку Справка.
И вот я теперь не знаю, на какую кнопку нажимать, и что за возможности потеряются. Файл выгрузки большой — столбцов много, мне можно менять значения только одного столбца. Подскажите, что значит это сообщение?