Add blank space automatically in a column

[Geoff Baker]

For example, In excel in cells A2 to A56 I've got words in them, I wanted to copy all the cells in column A to B in same order,but when the word "check" is encountered, a blank cell will be added below the cell with the word "check". How can I do this?Help...

EggHeadCafe - Software Developer Portal of Choice
Dr. Dotnetsky's Cool .NET Tips and Tricks #24
http://www.eggheadcafe.com/tutorial...b594-7431fe346dee/dr-dotnetskys-cool-net.aspx

 

[Gary''s Student]

Try this small macro:

Sub GeoffBaker()
Dim ra As Range, rb As Range, rCheck As Range, r As Range
Dim s As String
s = "check"
Set ra = Range("A2:A56")
Set rb = ra.Offset(0, 1)
Set rCheck = Nothing
ra.Copy rb
For Each r In rb
If InStr(r.Value, s) > 0 Then
If rCheck Is Nothing Then
Set rCheck = r
Else
Set rCheck = Union(r, rCheck)
End If
End If
Next
rCheck.Offset(1, 0).Insert Shift:=xlDown
End Sub

Macros are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To use the macro from Excel:

1. ALT-F8
2. Select the macro
3. Touch RUN

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm