|
|
![]() |
| Thread Tools | Search this Thread |
#1
| |||
| |||
How to find value in Excel column and then fill down to a particular row I need a macro that searches for a column in a data bank by heading. In my excel sheet have written a macro to format some data. However there is a section of the macro that fills a column with a formula from the 11th row to the last used row. When you use a range this way it sets the formula of ALL cells in that Range to that formula. I think it may have something to do with the .UsedRange.Rows.Count part. |
#2
| |||
| |||
Re: How to find value in Excel column and then fill down to a particular row Try the following code... this will search for a particular string (partial strings will also work) and then it will add 20 more rows of data to the last cell. I want to do a special fill down that goes to, but not past, the lowest filled cell on the spreadsheet. I can find the last-filled cell in a single column. Code: Sub Fill20Down() Dim rng As Range Set rng = ActiveCell.End(xlDown) Range(rng, rng.Offset(20, 0)) = rng End Sub |
#3
| |||
| |||
Re: How to find value in Excel column and then fill down to a particular row If you column is populated, then you would hit the End Key, then the down arrow. Select the cells in the column, starting in the row below the column heading. Choose Edit | Go To special button, Type an equal sign, Press the up arrow on the keyboard -- this will enter a reference to the cell above, If you want to select from the current cell down to the bottom, then hold down the shift key while you hit End, then the down arrow. |
#4
| |||
| |||
Re: How to find value in Excel column and then fill down to a particular row You may also use the following code in your program and let me know what output you get, this code i found on the internet. Code: Sub Fill_Empty() '--David McRitchie, 2003-07-24, fillempt.htm '--Macro version of -- Excel -- Data Entry -- Fill Blank Cells 'http://www.contextures.com/xlDataEntry02.html 'http://www.pcworld.com/shared/printable_articles/0,1440,9346,00.html Dim oRng As Range Set oRng = Selection Selection.Font.Bold = True Selection.SpecialCells(xlCellTypeBlanks).Select Selection.Font.Bold = False Selection.FormulaR1C1 = "=R[-1]C" oRng.Copy oRng.PasteSpecial Paste:=xlValues, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False End Sub |
![]() |
|
Tags: ecel forumla, excel macro, excel sheet |
Thread Tools | Search this Thread |
|
![]() | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How to fill color in to the Row of Microsoft Excel | Shaina Na | MS Office Support | 6 | 14-01-2012 01:29 PM |
How to find particular column name by using Excel Macro | Eleder | Windows Software | 5 | 05-04-2011 10:49 PM |
Find The Value of one column in another column | Charth | Windows Software | 3 | 23-02-2011 03:01 AM |
How to Add Cell Fill in Excel | peterjack | Windows Software | 1 | 09-02-2011 02:21 AM |
What is Fill Handle in Microsoft Excel | Chandranath | Windows Software | 3 | 25-06-2009 06:53 PM |