Insert row. The IsEmpty function does not see an empty string.

Question

The code below is designed to find the word "world", check if there is an empty line above it, if there is then an output from the macro, if not, then insert an empty line! Finally exhausted to look, well, what's wrong with this simple line? The macro itself inserts an empty string and re-follows it does not see it and again inserts a new empty string, and so on to infinity.

Sub Test() Cells.Find(What:="world", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate ActiveCell.Offset(-1, 0).Select If IsEmpty(Rows(ActiveCell.Row).Select) = True Then End Else ActiveCell.Offset(1, 0).Select Selection.EntireRow.Insert Selection.Rows.AutoFit End If End Sub

Accepted Answer · 2017-02-24T00:00:55

Incorrect use of the IsEmpty operator, it is intended for variables.

Addressing sheet objects is a slow operation. Selecting cells is optional.

 Sub Test2() Dim r As Range ' ячейку со словом - в переменную Set r = Cells.Find(What:="world", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart) ' если над "world" пусто, вставляем строку If Cells(r.Row - 1, r.Column).Value <> "" Then Rows(r.Row - 1).Offset(1, 0).Insert Set r = Nothing ' освобождаем память End Sub

Verification of the entire line depends on the specific data. If the range of columns is unknown, the boundary columns of the range should be determined in advance.

The simplest (and slowest) is to count values (sum, score on conditions ...) using the sheet function:

 If Application.WorksheetFunction.CountA(Range(Cells(r.Row - 1, 1), Cells(r.Row - 1, 50))) > 0 Then

You can cycle through the cells of the row:

 Sub Test2() Dim r As Range, c As Range Dim lClmn As Long Dim b As Boolean lClmn = ActiveSheet.UsedRange.Columns.Count ' столбцов в диапазоне Set r = Cells.Find(What:="world", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart) Set r = Range(Cells(r.Row - 1, 1), Cells(r.Row - 1, lClmn)) ' диапазон строки For Each c In r ' цикл по ячейкам диапазона If c.Value <> Empty Then b = True: Exit For ' поставили флаг и вышли из цикла Next c If b Then Rows(r.Row).Offset(1, 0).Insert ' вставляем строку Set r = Nothing ' освобождаем память End Sub

Thanks a lot, the first version of your code works, but why in the main, I see a non-working option?
I hurried a bit, your code works, but it determines for emptiness, only the Cell located above the one found, but you need to look through the whole line.

Insert row. The IsEmpty function does not see an empty string.

1 answer 1

More articles: