i have report pull everyday placed in awekward format. it's contains variable row count 4 columns organized unofficial tables based on name of each employee.
what have employee name in column b preceded 2 blank rows above , followed 1 blank row of data below.
what want accomplish loop through data, identify cells in column b <> blank, delete entire 2 rows below cell, , delete entire 1 row above cell.
below have far. not much:
sub test() dim currentsht worksheet dim startcell range dim lastrow long dim lastcol long dim integer set currentsht = activeworkbook.sheets(1) set startcell = currentsht.range("a1") lastrow = startcell.specialcells(xlcelltypelastcell).row lastcol = startcell.specialcells(xlcelltypelastcell).column = lastrow 1 if cells(i, "b").value <> "" end sub
without making major changes code, try this:
for = lastrow 1 step - 1 if cells(i, "b").value <> "" range(cells(i, "b").offset(1), cells(i, "b").offset(2)).entirerow.delete 'delete 2 below cells(i, "b").offset(-1).entirerow.delete ' delete 1 above you non-blank cell (ie cells(i,"b")). reference range in relation cell have, use offset.
so, , in order, select range of cells 1 below cell offset(1) 2 cells below offset(2)'. change range toentirerow` cells, , delete.
then select cell above offset(-1), select entirerow , delete.
