You cannot do this in a same column because of circular reference. But you can use another column to do it. I assume that you have these numbers in column A and from row 2 and you insert an empty column in B and you enter the following formula in B2. Then copy the rest of the rows of the column B from the cell B2. Then Hide your column A, you will get what you wanted in column B. =IF(A2',A2,IFERROR(IF(MATCH(B1,A3:A100,0)>0,B1,'),')) If you feel there could more than 100 empty rows. ![]() Then you may need to replace the 100 found in the formula to what is the maximum number of rows that you expect to be empty. Feb 26, 2018 - Would you like to spend less time in Excel spreadsheets? Then, place the mouse at the lower right corner of the two cells until the cursor. Enhance how efficiently you are able to automatically fill in data in sheets. In the sheet above, your boss wants you to fill in any blank cell with the string “N/A”. ![]() Chrome for mac os x 10.6.8. @Sandeep: The solution will go like this. First, you need a blank column adjacent to your data. For instance, if data is in column A to Z so take column AA 2. Enter 1 in the first cell adjacent to the data and 2 below it. Select these 1 & 2 and double click to auto-fill till the end of your data. Now copy the values entered in step 3 and go the first blank cell in this column AA and paste what you copied from above. Click Sort Ascending to sort the spreadsheet by the auto-fill values in column AA and when asked for expand the selection. Adobe web premium cs5 mac os x x. Delete the column AA and you are done. John @Anonymous great code. Outlook 365 for mac duplicate contacts free. It also fills to the right if you select a range greater than one column.I modified your code as follows so user can select a range: Sub FillBlankswithcellAboveandtoLeft() 'This code fills any blanks with what is immediately above and to left Dim C As Range Dim MyRange As Range Dim MyValue As Variant On Error Resume Next Application.DisplayAlerts = False Set MyRange = Application.InputBox(Prompt:= _ 'Select the range you want to step through and fill blanks with cell immediately above', _ Title:='Specify Range now.' , Type:=8) On Error GoTo 0 Application.DisplayAlerts = True If MyRange Is Nothing Then Exit Sub Else MyRange.Select For Each C In MyRange If C.Value = 0 Then C.Value = MyValue Else MyValue = C.Value End If Next C End If End Sub John.
0 Comments
Leave a Reply. |