MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Inserting cells based on values in that cell


Posted by Martin on January 10, 2001 8:34 AM

I have a column of data with some values which are greater than 100. In each cell which has a value of less than 100 I want to insert a blank cell to shift the cells in the next column to the right. Has anyone any ideas on how to do this using a macro??


Posted by Aaron on January 10, 2001 9:32 AM

Try This:

Dim i As Integer
On Error Resume Next
For i = 1 To 20

If ActiveSheet.Range("A" & i).Value < 100 Then
Range("A" & i).Insert Shift:=xlToRight
End If
Next
End Sub

This assumes that your data column is in A1:A20

Posted by Martin on January 11, 2001 2:12 AM

Thanks a lot Aaron...that worked a treat!! I've got a whole (65000 +) cells to work on but when I change the 1 To 20 to 1 To 65000, it doesn't seem to work..any ideas?? Or is there a quick way of doing it on a user selection of cells??

Cheers, Martin

Posted by Tim Francis-Wright on January 11, 2001 6:33 AM


In VBA, an Integer can't exceed 32767; change
Dim i As Integer

to

Dim i as Long

if you're going to use a large number like 65000.

--Tim F-W