VBA to count rows from specific cell and insert rows

  • Thread starter Thread starter Valerie
  • Start date Start date
V

Valerie

Within a macro I have a "found" cell (Find "US03" in certain column) which
can be on any row. I need code to count down 170 rows from this cell and
insert 2 rows. This is done from the top of the worksheet.

Thanks!!
Valerie
 
not sure where you want to start, but this may give you an idea on how to do
what you want

Worksheets("Sheet1").Range("A1").Offset(170).Resize(2).EntireRow.Insert
 
Hi,
try something like

DIm rg as range

''' where to search
set rg=activesheet.range("U:U")
''' search for 'found'
set rg=rg.find(what:="found", lookin:=xlvalues, lookat:=xlwhole)
''' process result
if rg is nothing then ''' was not fond
msgbox "Not found"
else
''' go 170 rows bellow that found cell
set rg=rg.offset(170)
''' resize to 2 rows
set rg= rg.resize(2)
''' insert 2 rows
rg.EntireRow.Insert xlShiftDown
end if
 
Works like a charm!! Thanks!!

sebastienm said:
Hi,
try something like

DIm rg as range

''' where to search
set rg=activesheet.range("U:U")
''' search for 'found'
set rg=rg.find(what:="found", lookin:=xlvalues, lookat:=xlwhole)
''' process result
if rg is nothing then ''' was not fond
msgbox "Not found"
else
''' go 170 rows bellow that found cell
set rg=rg.offset(170)
''' resize to 2 rows
set rg= rg.resize(2)
''' insert 2 rows
rg.EntireRow.Insert xlShiftDown
end if
Click to expand...
 
Back
Top