I’m back on Excel, so may have a few Excel questions of the next couple of weeks.

The first is:

How do I insert a blank row before every row that contains 9999 in column D?

mollwollfumble said:

I’m back on Excel, so may have a few Excel questions of the next couple of weeks.

The first is:

How do I insert a blank row before every row that contains 9999 in column D?

Happy with VBA?

I don’t think there is any way to do it without.

The Rev Dodgson said:

mollwollfumble said:

I’m back on Excel, so may have a few Excel questions of the next couple of weeks.

The first is:

How do I insert a blank row before every row that contains 9999 in column D?

Happy with VBA?

I don’t think there is any way to do it without.

Very Happy. Fingers crossed.

Okay. VBA editor is open. What do I do now?

mollwollfumble said:

Okay. VBA editor is open. What do I do now?

Alt f4

mollwollfumble said:

Okay. VBA editor is open. What do I do now?

Insert a new module if you don’t have any.

Copy and paste:

Sub insrow()
Dim cell As Range, CheckNext As Boolean
CheckNext = True
For Each cell In ActiveSheet.Columns(4).Cells

If cell.Value = 9999 Then If CheckNext Then cell.EntireRow.Insert CheckNext = False Else CheckNext = True End If End If Next cell

End Sub

Press F5 from the editor, or go to the worksheet and press alt-F8 and select insrow.

The Rev Dodgson said:

mollwollfumble said:

Okay. VBA editor is open. What do I do now?

Insert a new module if you don’t have any.

Copy and paste:

Sub insrow()
Dim cell As Range, CheckNext As Boolean
CheckNext = True
For Each cell In ActiveSheet.Columns(4).Cells

If cell.Value = 9999 Then If CheckNext Then cell.EntireRow.Insert CheckNext = False Else CheckNext = True End If End If Next cell

End Sub

Press F5 from the editor, or go to the worksheet and press alt-F8 and select insrow.

This editor messes up code.
Should be a new line after each “Then”, and for each new statement.

I can e-mail a file if you can’t get it to work.

The Rev Dodgson said:

The Rev Dodgson said:

mollwollfumble said:

Okay. VBA editor is open. What do I do now?

Insert a new module if you don’t have any.

Copy and paste:

Sub insrow()
Dim cell As Range, CheckNext As Boolean
CheckNext = True
For Each cell In ActiveSheet.Columns(4).Cells

If cell.Value = 9999 Then If CheckNext Then cell.EntireRow.Insert CheckNext = False Else CheckNext = True End If End If Next cell

End Sub

Press F5 from the editor, or go to the worksheet and press alt-F8 and select insrow.

This editor messes up code.
Should be a new line after each “Then”, and for each new statement.

I can e-mail a file if you can’t get it to work.

Don’t ever leave.

The Rev Dodgson said:

mollwollfumble said:

Okay. VBA editor is open. What do I do now?

Insert a new module if you don’t have any.

Copy and paste:

Sub insrow()
Dim cell As Range, CheckNext As Boolean
CheckNext = True
For Each cell In ActiveSheet.Columns(4).Cells

If cell.Value = 9999 Then If CheckNext Then cell.EntireRow.Insert CheckNext = False Else CheckNext = True End If End If Next cell

End Sub

Press F5 from the editor, or go to the worksheet and press alt-F8 and select insrow.

I was able to click “quote” here to get the original text you typed.

Worked perfectly. Six worksheets with about 6000 newlines on each.

And beautifully simple compared to some VBA code on the web.

mollwollfumble said:

The Rev Dodgson said:

mollwollfumble said:

Okay. VBA editor is open. What do I do now?

Insert a new module if you don’t have any.

Copy and paste:

Sub insrow()
Dim cell As Range, CheckNext As Boolean
CheckNext = True
For Each cell In ActiveSheet.Columns(4).Cells

If cell.Value = 9999 Then If CheckNext Then cell.EntireRow.Insert CheckNext = False Else CheckNext = True End If End If Next cell

End Sub

Press F5 from the editor, or go to the worksheet and press alt-F8 and select insrow.

I was able to click “quote” here to get the original text you typed.

Worked perfectly. Six worksheets with about 6000 newlines on each.

And beautifully simple compared to some VBA code on the web.

Oh yeah, I forgot about clicking quote to see the original.

Glad it worked :)

Peak Warming Man said:

The Rev Dodgson said:

The Rev Dodgson said:

Insert a new module if you don’t have any.

Copy and paste:

Sub insrow()
Dim cell As Range, CheckNext As Boolean
CheckNext = True
For Each cell In ActiveSheet.Columns(4).Cells

If cell.Value = 9999 Then If CheckNext Then cell.EntireRow.Insert CheckNext = False Else CheckNext = True End If End If Next cell

End Sub

Press F5 from the editor, or go to the worksheet and press alt-F8 and select insrow.

This editor messes up code.
Should be a new line after each “Then”, and for each new statement.

I can e-mail a file if you can’t get it to work.

Don’t ever leave.

+1

:)

The Rev Dodgson said:

mollwollfumble said:

The Rev Dodgson said:

Insert a new module if you don’t have any.

Copy and paste:

Sub insrow()
Dim cell As Range, CheckNext As Boolean
CheckNext = True
For Each cell In ActiveSheet.Columns(4).Cells

If cell.Value = 9999 Then If CheckNext Then cell.EntireRow.Insert CheckNext = False Else CheckNext = True End If End If Next cell

End Sub

Press F5 from the editor, or go to the worksheet and press alt-F8 and select insrow.

I was able to click “quote” here to get the original text you typed.

Worked perfectly. Six worksheets with about 6000 newlines on each.

And beautifully simple compared to some VBA code on the web.

Oh yeah, I forgot about clicking quote to see the original.

Glad it worked :)

> 6000
Oops, that was 60,000 newlines on each. Not something I could do by hand. :)

The application is processing radiosonde data.