Excel Challenge 1

[TommyP]

Hello,

I was wondering if it is possible for a macro to be able to insert a row after so many (user defined) rows? Say every fifth row I want to insert a blank row. Or every second row etc.

Second Question: Is it possible for a macro to delete the contents of every fifth cell or five cells in a row and leave the data in the sixth cell, continuing on to delete the next five cell contents and leaving the next sixth cell, and so on...?

 

[xlbo]

Yes and yes
1:
Sub InsertRows()
nRows = InputBox("Enter row gap&quot
fRow = Selection.Row
For i = fRow + nRows To Cells(65536, Selection.Column).End(xlUp).Row Step nRows + 1
Rows(i & ":" & i).EntireRow.Insert shift:=xlUp
Next i
End Sub

This kinda works but leaves rows at the end cos of the inserts - you should really move backwards for inserts but then you need to know where to start from etc
Perhaps a little more info would be useful

2:
sub clear5()
mRow = selection.row
for each c in range(cells(mRow,1),cells(mRow,range("IV" & mRow).end(xltoleft).column))
if c.column/6 = int(c.column/6) then
'do nothing
else
c.clearcontents
end if
next
end sub

or something similar - Again, more info would be useful
Rgds
~Geoff~