Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How many way to represent the blank space? | Excel Discussion (Misc queries) | |||
Can I automatically add space after ea. row ("Space After" in Word | Excel Discussion (Misc queries) | |||
why does blank space appear at end of number? | Excel Discussion (Misc queries) | |||
add blank space in cell if first name is not blank | Excel Worksheet Functions | |||
I want a blank space on a worksheet when the question is blank? | Excel Discussion (Misc queries) |