VBA - Autofill To First Blank Adjacent Column?

Mar 5, 2014

I want to write some VBA code that simulates the double click of the square in the lower right of a selected cell. When you double click it, it fills every cell in column A until the first blank in column B. I was able to find this code searching around:

Code:
Selection.AutoFill Destination:=Range("A10:A" & Range("B10" & Rows.Count).End(xlUp).Row)
Selection.Copy

The issue with the above is that it copies the data all the way down to the LAST row in column B with data.

I want to have the macro stop when it finds the FIRST blank cell in column B.

Just as an example, the last row of data in column B is 100, but in row 67 the cell is blank, so I want it to stop at row 66.

View 2 Replies


ADVERTISEMENT

VBA If Cell Is Blank Import Value From Adjacent Column

Nov 14, 2006

I'd like a macro that cycles down 50000 rows of column B. If it comes across a blank cell it will import the value from its adjacent cell in column A. then the macro will move onto the next cell.

View 9 Replies View Related

Find First Blank Cell In Column & Return Adjacent Date Less Than Or Equal To Today

Apr 4, 2008

how to make the data look like a table with three columns. Other than the date, it is space delimited. I have a tracking spreadsheet where Column A is populated with dates for the year. Column C contains daily values.

I don't always start entering daily values on the first day of the year, e.g., this year the first value in Column C corresponds to March 9. All values in Column C are contiguous - there are no blank cells until the value in Column A is greater than today's date code. I would like to use a formula (rather than VBA) to look down Column C and find the first non-blank entry where the value in Column A is less than or equal to today(). In this case, the formula should return the value for March 9, 2008.

CREATE TABLES LIKE BELOW?Column A Column B Column C

March 1, 2008Saturday
March 2, 2008Sunday
March 3, 2008Monday
March 4, 2008Tuesday
March 5, 2008Wednesday ...................

View 4 Replies View Related

Autofill Only If Adjacent Row Has Info...

Nov 4, 2008

Below is my current code. I created this to work for a spreadsheet I had. Works mint. Then I use in another spreadsheet which is almost identical but has less rows. Everything works, but then im left with a bunch of extra rows that have DIV error. Rather than select range 2:34, is there a way to select the entire column, and autofill only if the column next to it has a number in it?

View 6 Replies View Related

AutoFill Down To Adjacent Columns Last Used Row

Jan 24, 2008

I have got 5 columns. What i intend to do is to loop to the last data cell in column d,e and autofill it down from there till the last row in column a,b,c. What my code does now is to autofill the entire column c,d from the 1st cell and not from the last data entry cell.

Dim r As Range
Dim lRow As Long
lRow = WorksheetFunction.Max(Range("A65536").End(xlUp).Row, _
Range("B65536").End(xlUp).Row, Range("C65536").End(xlUp).Row)
Set r = Range("d1:e1")
r.AutoFill Range("D1:E" & lRow)

I had searched and tried dozens of variations but just can't get it right.

View 4 Replies View Related

IF Blank Or If Not Blank Function That Works With Adjacent Cell With Formula In It?

Aug 12, 2014

I have one column that contains an If statement formula and would like the next column to then work off of the first column (i.e. if that 1st column returns a value then then adjacent column uses that result).

What is happening now is that it is returning #value (because I guess technically the cell isn't blank?)

View 5 Replies View Related

Insert Blank Row If Adjacent Cell Value Is Blank

Jan 7, 2013

If the value of the adjacent cell in column A is blank, insert a blank row through Column B:J.

View 9 Replies View Related

Autofill Blank Cells

Jul 8, 2009

I have a column of names with some spaces e.g. A1: BROWN, A2:empty, A3:empty, A4:SMITH, A5: empty, A6:JONES etc. All the empty cells have the same name as the one above i.e. A2 and A3 should also read "BROWN". A5 should read "SMITH" and so on down the column. Is there away to create a formula that does this automatically. I've tried a simple if command in a duplicate column which looks at the first column and tests for a blank - if it finds one it enters the name from the cell above - but this only works once and some of the empty spaces need to be filled more than once e.g. A2 & A3 above.

View 2 Replies View Related

Autofill Until Non-blank Cell Macro

Jan 10, 2009

I have searched the forum a few times and haven't seen an exact example of what I am trying to do, so I hope this isn't a duplicate.

I am looking for a macro to copy a cell and autofill it down the column until it gets to a non-blank cell. When it reaches that cell it needs to then copy THAT cell down until it reaches another non-blank cell... this could happen just a couple times, a couple dozen times, or only the very first time.

So for example:

In cell B2 there is text "Data1"
In cell B13 there is text "Data2"
In cell B25 there is text "Data3"

I would like the macro to search column B to find "Data1", and then copy it down the column until it reaches B13, then copy "Data2" down until B25, where it would then copy "Data3" down until the end of the document. The location of the data in column B varies, and the number of cells with data in column B will vary as well.

View 9 Replies View Related

Excel 2010 :: Autofill Entering Data Against Blank Cells

Jan 23, 2014

In earlier versions if I used the double clicked the autofill handle it would only fill down to the next adjacent cell containing data. Now it fills down to the bottom of all the data regardless of whether there is a blank cell or not e.g

X X X Z
X X X Z
X X X Z
X X
X X X

Now this happens

X X X Z
X X X Z
X X X Z
X X ...Z
X X X Z

How can I stop this as I need to enter different data in the next section?

View 1 Replies View Related

Sum 3 Non-adjacent Cells Some Are Blank?

Mar 5, 2014

I am trying to sum 3 non-adjacent cells. Some cells are blank, not always in the same row / column, and all are the result of vlookups. I tried: E = I+M+Q, but the result is #value!

-In column E (respectively F, G, H) that's where I want the result of the sum

-In column I (respectively, J, K, L), depending on the row, some cells are blank and some cells contain a number. That's the first term of my sum.

-In column M (respectively N, O, P), I have also blank and non-blank cells, depending on the row. This is the second term of my sum.

-In column Q (respectively R, S, T), also blank or non-blank cells. This is the third term of my sum.

I will need to extend this formula to F=J+N+R, G=K+O+S, H=L+P+T, and probably also a sum with 4 terms where E=I+M+Q+U etc.

View 2 Replies View Related

Only Countif Cells Adjacent To Columns Contains A Blank?

Apr 22, 2014

I have a formula (below) where I want it to count the cells that contain a date that is less than or equal to today, but only when the adjacent cell is blank. The latter part of the formula works, but I'm struggling to tell it to only do the count when cells in column I are blank;

=IF('QS12'!I10:I3120="",COUNTIF('QS12'!H10:H3120,">="&TODAY()))

View 9 Replies View Related

Show Text If Adjacent Cell Is Not Blank

Aug 8, 2008

I am looking for a piece of code that I can use.

Basically if cell A2 is non blank then I want cell C2 to display My Text. I want to do this for every cell down to about A250.

View 4 Replies View Related

Excel 2007 :: Autofill Column B With Sequential Values Based On Value In Column A

Nov 27, 2011

Autofill column B with sequential values based on whether value in column A changes its value.

I would like to autofill column 'B' with sequential values (i.e. GenoMap1, Genomap2, Genomap3,... GenoMap10, GenoMap11, GenoMap12,... GenoMap104, GenoMap105, etc...), but changing to the sequential GenoMap# only when the value in column A changes.

This is what I imagine.

A1 "Alfiero", B1 "GenoMap1"
A2 "Alfiero", B2 "GenoMap1"
A3 "Alfiero", B3 "GenoMap1"
A4 "Allocati", B4 "GenoMap2"
A5 "Amaranto", B5 "GenoMap3"
A6 "Amaranto", B6 "GenoMap3"
A7 "Amaranto", B7 "GenoMap3"
A8 "Ambrosiano", B8 "GenoMap4"
A9 "Ambrosiano", B9 "GenoMap4"
A10 "Ambrosiano", B10 "GenoMap4"
A11 "Ambrosiano", B11 "GenoMap4"

I listed examples above of GenoMaps higher than 10 and 100 to show how I need them numbered.

I'm using MS Excel 2007 in Windows 7.

View 2 Replies View Related

Formula To Keep Adjacent Cell Blank When No Date Is Added

Jan 23, 2014

I have a formula in my spreadsheet that shows dates for servicing of products. Column F show's Date Last Serviced and the adjacent column (G) then shows Date Next Service. When there is nothing on the F column's cell then the date that appears on my spreadsheet is 31/12/00. It looks messy and I would rather have a formula to make it so that if the F column is blank, then the adjacent cell in G column should stay blank also.

To automatically calculate the next service due date, I have variants of this formula that creates the dates in G column:

=DATE(YEAR(F4)+1,MONTH(F4),DAY(F4))

I tried adding as an extra part, for example:

=DATE(YEAR(F4)+1,MONTH(F4),DAY(F4))&If(F4 = 0, "", F4)

It always brings back the answer of 366, and does not make the cell blank...

View 2 Replies View Related

Conditional Formatting Based On Adjacent Cell Not Being Blank

Mar 6, 2014

I want AB1 to highlight red if date in S1 is not blank and N1 doesnt equal AA1. Is this possible?

View 1 Replies View Related

Fill In Blank Cells If Adjacent Cell Has Value Then Do For Next Set Of Data

Feb 19, 2014

I'm trying every conceivable angle I can think of in vba and coming up empty. What I have is a spreadsheet similar to this:

Column A Column B

1. 1234567
2. 2345678
3. 3456789
4. 4567890 A0001
5.
6. 9876543
7. 9876543
8. 9876543
9. 1234578
10. 3456789 A0002

What I would like to do is to fill in B1:B3 with A0001 and B6:B9 with A0002. The number of rows in each set of data will vary, but the identifier (A000X) will always be located in the row in column B adjacent to the last entry for that set of data in column A and there will always be empty cells between data sets. The number of data sets will also vary.

View 5 Replies View Related

Stopping Data Entry If Adjacent 5 Cells Are Blank

Feb 10, 2009

I've got six columns with drop-down lists in every cell. I don't want column six to be selectable if the first five drop downs don't have data in them too.

Equally, I want the data in column six to be undone if columnns 1-5 are then unselected. Can I do this?

I know that using data validation or VBA is an option but I'm not sure exactly how to set it up this way. I also don't want anyone screwing with what can and can't be entered according to these rules - I want to keep it watertight!

View 9 Replies View Related

Delete Rows Based On Adjacent Blank Cells

Aug 8, 2008

I would like a macro that deletes rows based on having two blank cells in adjacent columns. I have achieved this with the following code however i need it to only delete rows below a certain row. How would i achieve this?

Sub DeleteBlankARows()
Dim r As Long
For r = Cells(Rows.Count, 30).End(xlUp).Row To 1 Step -1
If Cells(r, 3) = "" And Cells(r, 4) = "" Then Rows(r).Delete
Next r
End Sub

View 2 Replies View Related

Insert Column, Move Another Column Into It And Autofill

Feb 3, 2009

I have attached a sample spreadsheet – what I want to do is insert a column before the current column A and move the current column D in to it and auto fill for every non blank cell in column B. Also, there are different values in column D such as “B03” ,”B41” etc – so for example, it should auto fill B03 until it comes across either a blank cell in column B or it comes across a “B41” and then it should do the same until it comes across a “B42” or a blank cell again. This needs to continue till the end of the report.

View 2 Replies View Related

Autofill Column B Based On Column A Entries

Oct 9, 2009

I have a Listbox that outputs data to Column A. Is there a way to auto populate Column B with 4 items for each Entry in Column A?
The 4 items that will populate in Column B will always be the same.

I have attached an example to better explain what I am trying to describe.

View 6 Replies View Related

Autofill Column To Repeat Set Of Data From Another Column

Aug 10, 2013

I want to Autofill a Column C to repeat a set of data in Column A, if Column B contains an integer, as shown below. Being able to Autofill this data allows me to have a dynamic table for use on multiple projects that may have a different amount of rows in Column B.

Outflow % Table
25
50

[Code]......

View 6 Replies View Related

Autofill Row Instead Of Column?

Jul 11, 2014

I want to change the following code so that it fills a row instead of a column

[Code] ......

This fills column "A" top down but I now would like to fill row "1" left to right.

View 2 Replies View Related

AutoFill More Than One Column

Sep 17, 2008

I have code to fill down to the last row with data for 1 column Can I adapt the code to fill down more than 1 column. current code for 1 column

View 2 Replies View Related

AutoFill To Last Row In Column A

Feb 10, 2010

the code as shown below so that in column A of worksheet: Data to Text I need an AutoFill from cell A1 to the last row. The value in cell A1 needs to be: 1

If there are more than 1 records then I need an AutoFill from cell A1 to the last row. However, the value in cell A1 needs to be 1, value in cell A2 needs to be 2, value in cell A3 needs to be 3 etc...

View 2 Replies View Related

Autofill Vertical List Horizontally With 2 Blank Cells In-between Each List Item

Feb 14, 2014

I have a list in column L2:L352. I would like to be able to start at AD2 and drag horizontally so that AD2 = L2, AE2 = <BLANK>, AF2 = <BLANK>, AG2 = L3, AH2 = <BLANK>, AI2 = <BLANK>, AJ2 = L4, etc.... until L352 has been autofilled.

View 2 Replies View Related

Formula To Pull Latest Date From One Column Based On Entry In Adjacent Column

Jun 6, 2014

Any way to construct a formula in excel that will look at a reference in one column and find the latest date from the data in an adjacent column for that specific reference?

Below is an exctract from a much larger sheet of the columns in question.

The result in the last column should be 21/05/2014 for anything with D.O.001 in the second column and 15/05/2014 for anything with D.O.002.

Date Decision agreed
Disposal Order
Latest Decision date for D.O.

06/05/2014
D.O.001

[Code] ........

View 6 Replies View Related

Formula To Total Values In Column Matching Text In Adjacent Column

Dec 20, 2013

Formula(s) to do as explained in the attached example.

Example_formula.xlsx

View 9 Replies View Related

Formula To Match Text In Column And Retrieve Adjacent Column Value?

Mar 5, 2014

I need a formula to run down a column DCapture.JPG (starting at 142), when it finds the last entered value it needs to display the corresponding value from column J into cell AA21.

If you see the attached photo, the last entered data in column D would be 1, AA21 would be saying -30 (J205)

Capture.JPG

View 6 Replies View Related

Copy Cells From Column To Adjacent Column If Bold Or Empty

May 20, 2008

I have a worksheet on which the data is already grouped. At the top of each group is a row that contains only the group name. Since the rest of that row is blank, I want to use a blank cell on that row as a reference, then copy the group name to a newly created column, then fill that column down to the next group.

The goal is to create a column that contains the group name, rather than just having the group name as a " header" at the top of each group.

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved