If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Add blank space automatically in a column
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/tutorials...-cool-net.aspx |
#2
|
|||
|
|||
Add blank space automatically in a column
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 -- Gary''s Student - gsnu200904 "Geoff Baker" wrote: 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/tutorials...-cool-net.aspx |
Thread Tools | |
Display Modes | |
|
|