Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
blank rows filling (Urgent)
Hi,
all Here is a example. I have three columns in excel.There are some blank cells and rows. A B C SMH SMHP CPR MDR SLK CPD DHG STG STG Here is how it should look after filling up the blank columns Col A Col B Col C SMH SMHP CPR SMH SMHP CPR SMH SMHP MDR SMH SMHP MDR SMH SMHP MDR SMH SMHP SLK CPD DHG STG CPD DHG STG Right now I am doing it manually. Is there a way I can do it by writing some VBA code or macros Any help would be highly appreciated? -- Kittie |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
blank rows filling (Urgent)
Try this:
Select your entire data range <Edit<Go to Click the [Special Cells] button Check: Blanks Click the [OK] button Then...while those blanks are selected... Type = then press the UP ARROW key one time Then..holding down the [Ctrl] and [Shift] keys...press the [Enter] key That should cause every blank cell to reflect the first available value above it. Note: to "hard code" those values... Select the entire data range <Edit<Copy <Edit<Paste Special Check: Values Click the [OK] button Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Lolly" wrote: Hi, all Here is a example. I have three columns in excel.There are some blank cells and rows. A B C SMH SMHP CPR MDR SLK CPD DHG STG STG Here is how it should look after filling up the blank columns Col A Col B Col C SMH SMHP CPR SMH SMHP CPR SMH SMHP MDR SMH SMHP MDR SMH SMHP MDR SMH SMHP SLK CPD DHG STG CPD DHG STG Right now I am doing it manually. Is there a way I can do it by writing some VBA code or macros Any help would be highly appreciated? -- Kittie |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
blank rows filling (Urgent)
Loly,
If you would like to work with macro: Sub CopyInSelection() 'Macro fills in empty cells in the selection by copying each 'non-empty cell from upper left corner into all succeeding cell 'until coming across the next non-empty one. If the number of 'columns in the selection is greater or equal to the number of 'rows, the filling runs downwards, and vice versa. 'Petr Bezucha, 2005 Dim I As Integer, R1 As Integer, R2 As Integer Dim J As Integer, C1 As Integer, C2 As Integer With Selection R1 = .Row R2 = .Rows.Count C1 = .Column C2 = .Columns.Count End With S = "" If R2 < C2 Then For I = R1 To R2 For J = C1 To C2 GoSub Action Next J Next I Else For J = C1 To C1 + C2 - 1 For I = R1 To R1 + R2 - 1 GoSub Action Next I Next J End If Exit Sub Action: Set C = Cells(I, J) If IsEmpty(C) Then C.Value = S Else S = C.Value End If Return End Sub Regards -- Petr Bezucha Lolly pÃ*Å¡e: Hi, all Here is a example. I have three columns in excel.There are some blank cells and rows. A B C SMH SMHP CPR MDR SLK CPD DHG STG STG Here is how it should look after filling up the blank columns Col A Col B Col C SMH SMHP CPR SMH SMHP CPR SMH SMHP MDR SMH SMHP MDR SMH SMHP MDR SMH SMHP SLK CPD DHG STG CPD DHG STG Right now I am doing it manually. Is there a way I can do it by writing some VBA code or macros Any help would be highly appreciated? -- Kittie |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Filling in blank rows | Excel Discussion (Misc queries) | |||
Filling In Every Blank | New Users to Excel | |||
Filling Blank Cells | Excel Discussion (Misc queries) | |||
Filling in Blank Cells | New Users to Excel | |||
Filling Blank Cells | Excel Programming |