Insert row
I want to use code to insert a row above a row where the first value found
in column D equals "G" I do not want to insert an empty row for each "G" found. Perhaps the code is similar to code I am already using where rows are deleted. Any help is appreciated. Pat Private Sub DeleteRows_Click() Dim X As Long Dim Y As Long Y = Range("A65536").End(xlUp).Row 'y= find the last non empty cell in column A For X = Y To 1 Step -1 'Lcase gets the lower case of the word If LCase(Cells(X, 1).Value) = "s" Then Rows(X).Delete End If Next X End Sub |
Insert row
Dim rng as Range, lastrow as Long
lastrow = cells(rows.count,4).End(xlup).row set rng = Range("D1") do while ucase(rng) < "G" and rng.Row <= lastrow rng = rng.offset(1,0) Loop if Ucase(rng.Value) = "G" then rng.Entirerow.Insert End if this will insert a row above the first G found in column D. Is that what you want? -- Regards, Tom Ogilvy "Pat" wrote in message ... I want to use code to insert a row above a row where the first value found in column D equals "G" I do not want to insert an empty row for each "G" found. Perhaps the code is similar to code I am already using where rows are deleted. Any help is appreciated. Pat Private Sub DeleteRows_Click() Dim X As Long Dim Y As Long Y = Range("A65536").End(xlUp).Row 'y= find the last non empty cell in column A For X = Y To 1 Step -1 'Lcase gets the lower case of the word If LCase(Cells(X, 1).Value) = "s" Then Rows(X).Delete End If Next X End Sub |
Insert row
Hi, Tom
I have tried your code but it hangs once the commandbutton is clicked. Excel has to be closed via Ctl-Alt-Del. What could be happening? Pat "Tom Ogilvy" wrote in message ... Dim rng as Range, lastrow as Long lastrow = cells(rows.count,4).End(xlup).row set rng = Range("D1") do while ucase(rng) < "G" and rng.Row <= lastrow rng = rng.offset(1,0) Loop if Ucase(rng.Value) = "G" then rng.Entirerow.Insert End if this will insert a row above the first G found in column D. Is that what you want? -- Regards, Tom Ogilvy "Pat" wrote in message ... I want to use code to insert a row above a row where the first value found in column D equals "G" I do not want to insert an empty row for each "G" found. Perhaps the code is similar to code I am already using where rows are deleted. Any help is appreciated. Pat Private Sub DeleteRows_Click() Dim X As Long Dim Y As Long Y = Range("A65536").End(xlUp).Row 'y= find the last non empty cell in column A For X = Y To 1 Step -1 'Lcase gets the lower case of the word If LCase(Cells(X, 1).Value) = "s" Then Rows(X).Delete End If Next X End Sub |
All times are GMT +1. The time now is 07:26 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com