Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy selected cell in column and fill in blanks
Greetings, I am a novice at programming and have been reading the FAQs trying to solve a problem I have. I need to fill in a column's various "blank cell" of several hundred rows in the following manner: 1. Select cell of column with text (is the name of individual working project) 2. Copy cell and paste in all empty cells below text of that column. 3. Upon reaching next cell in column with text, stop pasting, select the cell with new text, repeat steps 1-3. I figured I needed a fail safe like: If blank cells = 15, end sub. Otherwise it would keep running. I have started with Excel "record a Macro" function and got the below Sub FILLinBLANKS() ' ' FILLinBLANKS Macro ' Macro recorded 6/10/2005 ' ' Keyboard Shortcut: Ctrl+j ' Cell.Select Selection.Copy Range("B13").Select ActiveSheet.Paste Range("B14").Select ActiveSheet.Paste Range("B15").Select ActiveSheet.Paste End Sub but of course it is putting stuff in B13:B15. and not doing what I need it do with looping, copying text cell, filling in blank cells of the column below the text cell, then selecting the next text cell in column after the blanks are filled, and repeating process. thanks for any replies, bluestar "ben" -- Bluestar ------------------------------------------------------------------------ Bluestar's Profile: http://www.excelforum.com/member.php...o&userid=24326 View this thread: http://www.excelforum.com/showthread...hreadid=379295 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy selected cell in column and fill in blanks
try like:
Sub FillBlanks() Dim rngCol As Range, rngBlank As Range, rngArea As Range If Not TypeOf Selection Is Range Then Beep: Exit Sub Set rngCol = Selection 'expand to entire column If rngCol.Count = 1 Then Set rngCol = rngCol.EntireColumn 'limit to usedrange Set rngCol = Intersect(rngCol, ActiveSheet.UsedRange) On Error Resume Next 'find blank cells Set rngBlank = rngCol.SpecialCells(xlCellTypeBlanks) On Error GoTo 0 If rngBlank Is Nothing Then Beep: Exit Sub 'Process each area 'copy value in cell above to blank cells application.screenupdating=False For Each rngArea In rngBlank.Areas rngArea.Value = rngArea.Cells(0, 1).Value Next application.screenupdating=true End Sub -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam Bluestar wrote : Greetings, I am a novice at programming and have been reading the FAQs trying to solve a problem I have. I need to fill in a column's various "blank cell" of several hundred rows in the following manner: 1. Select cell of column with text (is the name of individual working project) 2. Copy cell and paste in all empty cells below text of that column. 3. Upon reaching next cell in column with text, stop pasting, select the cell with new text, repeat steps 1-3. I figured I needed a fail safe like: If blank cells = 15, end sub. Otherwise it would keep running. I have started with Excel "record a Macro" function and got the below Sub FILLinBLANKS() ' ' FILLinBLANKS Macro ' Macro recorded 6/10/2005 ' ' Keyboard Shortcut: Ctrl+j ' Cell.Select Selection.Copy Range("B13").Select ActiveSheet.Paste Range("B14").Select ActiveSheet.Paste Range("B15").Select ActiveSheet.Paste End Sub but of course it is putting stuff in B13:B15. and not doing what I need it do with looping, copying text cell, filling in blank cells of the column below the text cell, then selecting the next text cell in column after the blanks are filled, and repeating process. thanks for any replies, bluestar "ben" |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy selected cell in column and fill in blanks
Many Many Many thanks Highest regards, blu -- Bluesta ----------------------------------------------------------------------- Bluestar's Profile: http://www.excelforum.com/member.php...fo&userid=2432 View this thread: http://www.excelforum.com/showthread.php?threadid=37929 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Fill in Blanks with immediately above cell value | Excel Discussion (Misc queries) | |||
Fill blanks down column with cell above | Excel Discussion (Misc queries) | |||
Copy/Fill cell values in a column | Excel Worksheet Functions | |||
Fill column blanks from last non-blank cell | Excel Discussion (Misc queries) | |||
Help with copying dynamic column selected based on remote cell value and dynamic formula fill | Charts and Charting in Excel |