нужны идеи

[eol]

Добрый день.
Задачка следующая. Есть лист "спецификация" (список товара с ценами и прочими параметрами), ее забивает менеджер. Есть лист спецификация к договору, этот лист я планирую экспортировать в ворд, а формировать его по листу "спецификация".
Ниже привожу кусок из файла. В файле фигурирует много сложных формул (большинство из них я убрал, оставил пару для примера). Сложность в том, что я блокирую часть ячеек (белые ячейки, в оригинальном файле их довольно много) для редактирования. Тогда происходит следующее: если менеджеру нужно удалить какую-либо позицию, он вынужден либо делать контрл+с и вставлять все что было ниже на столбец выше. Либо както очищать строки (удалить то он их не может, т.к. я заблокировал, чтобы он не испортил формулы), что не удобно, т.к. блокированные колонки перемежаются с неблокированными. При этом если завязать лист спецификация с договорной спецификацией формулами, будут образовываться #ссылка при удалении, либо формулы будут портиться. Можно, конечно, дать имя всей колонке и ссылаться на всю колонку, но проблему это не решает.

В итоге что мы имеем: менеджер не может удалять колонки, значит, если он будет их просто очищать, будут плеши в спецификации. Конечно, можно через макрос вывести все на другую страницу без этих дыр, но и это не решение, ведь сама спецификация тогда будет выглядеть отвратно.

Что пришло в голову, при выделении ячейки будет появляться кнопка, нажимаем ее - строка с ней очищается и все строки ниже поднимаются на одну вверх. Конечно, если надо таким образом удалить строк 100, будет неудобно, но все равно лучше чем ничего.

Задача в итоге такая: как сделать так, чтобы менеджер мог без проблем удалять строки, при этом это бы не убивало формулы, протянутые вниз в некоторых колонках (возможно формулы перевести на вба?)

Надеюсь на ваши идеи. Спасибо.

[kuklp]

http://www.cyberforum.ru/vba/thread631771.html
http://www.excelworld.ru/forum/3-176-1
Из этого можно склеить.

[eol]

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

http://www.cyberforum.ru/vba/thread631771.html
http://www.excelworld.ru/forum/3-176-1
Из этого можно склеить.

Спасибо. В принципе да, пожалуй можно. Код по первой ссылке был полезен, однако следующий код дает ошибку 400.
Sub www2()
Dim s$, r As Range
s = ActiveCell.Address
Set r = Range(Sheets(2).Range(s), Sheets(2).Range(s).End(xlToRight))
Range(s).EntireRow.Insert CopyOrigin:=xlFormatFromLeftOrAbove
r.Copy: r.Insert xlDown
Application.CutCopyMode = False
End Sub
Я его позже запустил, но он почему-то все равно добавляет 2 строки и глючит малость.

Как я вижу решение: сделать кнопку на удаление строки. Она будет:
1) удалять строку с 1го листа
2) удалять ее с листа 2, чтобы там не было строк вроде #ссылка
3) добавлять на оба листа в самый конец дополнительную строку с формулами, чтобы при длительной работе оставались юзабельные строки

Возможно я изначально делаю не рационально. Тоесть у меня, допустим, в колонке с формула а+в, я протягиваю с запасом на 500 строк, но если их постоянно удалять они рано или поздно закончатся.

[eol]

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

Спасибо. В принципе да, пожалуй можно. Код по первой ссылке был полезен, однако следующий код дает ошибку 400.
Sub www2()
Dim s$, r As Range
s = ActiveCell.Address
Set r = Range(Sheets(2).Range(s), Sheets(2).Range(s).End(xlToRight))
Range(s).EntireRow.Insert CopyOrigin:=xlFormatFromLeftOrAbove
r.Copy: r.Insert xlDown
Application.CutCopyMode = False
End Sub
Я его позже запустил, но он почему-то все равно добавляет 2 строки и глючит малость.

Как я вижу решение: сделать кнопку на удаление строки. Она будет:
1) удалять строку с 1го листа
2) удалять ее с листа 2, чтобы там не было строк вроде #ссылка
3) добавлять на оба листа в самый конец дополнительную строку с формулами, чтобы при длительной работе оставались юзабельные строки

Возможно я изначально делаю не рационально. Тоесть у меня, допустим, в колонке с формула а+в, я протягиваю с запасом на 500 строк, но если их постоянно удалять они рано или поздно закончатся.

покопался еще, как я понял следующий макрос
Public Sub www()
Dim s$, a
a = Sheets(2).[a1].CurrentRegion.Columns(6).Formula
s = ActiveCell.Address
Sheets(2).Range(s).EntireRow.Delete
Sheets(1).Range(s).EntireRow.Delete
Sheets(2).[f1].Resize(UBound(a) - 2) = a
End Sub

делает следующее:
запоминает все формулы в 6ой колонке, удаляет строки на листах, расширяет диапазон с f1 на всю колонку (минус 2 строки, в данном случае это итого и удаленная строка), а далее возвращает формулы
Вариант, но не совсем подходит под мою задачу, к сожалению.

Вообще меня больше волнует как дать пользователю возможность удалять строки (или хотя бы создать видимость этого путем очищения и копирования ячеек) при заблокированном изменении столбцов с формулами на листе.

[eol]

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

Либо еще вариант убрать все формулы с листа в vba, считать их по событию изменения листа, тогда вроде бы проблема полностью пропадает. Но формулы там сложные и в вба их переводить вручную не хочется.

[DiemonStar]

А так долго снять защиту?

Sub aa()
  ActiveSheet.Unprotect
  Rows(ActiveCell.Row).Delete Shift:=xlUp
  ActiveSheet.Protect
End Sub

[eol]

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

А так долго снять защиту?

Sub aa()
  ActiveSheet.Unprotect
  Rows(ActiveCell.Row).Delete Shift:=xlUp
  ActiveSheet.Protect
End Sub

ну защиту снять можно, а как формулу восстанавливать?

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

лист меняется - срабатывает код - и тп, те надо ограничивать область срабатывания, както тоже некрасиво


пока пришел к такому, но мне не нравится, думал покопать в сторону переноса всех вычисляемых форум на вба, но забыл где я видел пример, когда события ограничивались изменениями определенного диапазона, плюс в этом же примере был код, отображающий кнопку, в ячейку, в которую тыкнули
Private Sub CommandButton1_Click()
Dim s, a, last_Row, rng

s = ActiveCell.Row

last_Row = Cells(Rows.Count, "A").End(xlUp).Row

Set rng = Sheets("Ñïåöèôèêàöèÿ").Range(Cells( s, 1), "N500")
Sheets("Ñïåöèôèêàöèÿ").Range(Cells( s, 1), "N500").Copy
Cells(s - 1, 1).Select
Paste
Cells(s, 1).Select
End Sub

[kuklp]

Чтоб код не зацикливался, вначале макроса отключают события:
application.enableevents=false
В конце возвращают в true.

[eol]

вот что получилось, в целом устраивает:
Private Sub CommandButton1_Click()
Dim s1, s2, a, last_Row, rng
s1 = ActiveCell.Row
s2 = ActiveCell.Column

Sheets("Ñïåöèôèêàöèÿ").Range(Cells( s1 + Selection.Rows.Count, 1), "N500").Copy
Cells(s1, 1).Select
Paste
Cells(s1, s2).Select
End Sub

[eol]

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

Чтоб код не зацикливался, вначале макроса отключают события:
application.enableevents=false
В конце возвращают в true.

ясно, спасибо!