VBA Macro to Find Next Blank Cell 3

[MyFlight]

I have a Multipage Worksheet I am trying to set up to a
Pricing Matrix. The Main Problem I have is two-fold.

First:
I need to have Excel Move to the Next Blank row for
Data Input.

Second:
I will need to use this Row Refererence through various Macros located within the Same Worksheet.

EXAMPLE:

A Few Givins:

On the MAC Types Worksheet Cell on CBX MAC Worksheet

C7 (Never Changes) A2 (Row Changes)
C9 (Never Changes) B2 (Row Changes)
C11 (Never Changes) C2 (Row Changes)
C13 (Never Changes) D2 (Row Changes)
C15 (Never Changes) E2 (Row Changes)
C17 (Never Changes) F2 (Row Changes)

ETC.

Sub Mac_Selection()
;This is where the Data is Input and copied from
Sheets("MAC Types&quot.Select
Range("C7&quot.Select
Selection.Copy

;This is where the Data is Pasted to (The ROW will always
be the same until the End of Macro
Sheets("CBX MAC&quot.Select
Range("A2&quot.Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
End Sub


After exiting this Sub I would like to be able to keep the
Row Reference on the CBX MAC Worksheet, Until Such time as
I have completed all data entry.

Any and All help will be greatly appreciated.

 

[RobBroekhuis]

Assuming your data table starts in A2, with no blank cells, you can do

(outside of your macros)
public MyRow as integer

MyRow=range("A2&quot.end(xldown).row+1

and then

Sheets("MAC Types&quot.Range("C7&quot.Copy
Sheets("CBX MAC&quot.cells(MyRow,1).PasteSpecial xlValues

for column A, etc.
Rob

 

[VBAjedi]

range("A2&quot.end(xldown).row+1 works great as long as you never have any blanks in the column you are checking. If you may sometimes have blanks in that column, even though the row is not empty, you can check another cell on the same row (in my example, column B) with the following code:

FirstRow = 2
FindLast:
MyRow=range("A" & FirstRow).end(xldown).row
If Len(Range(&quot;B&quot; & MyRow).Value) <> 0 Then 
   FirstRow = MyRow
   Goto FindLast:
End If
MyRow = MyRow + 1 ' sets MyRow to first empty row

I use this approach often, sometimes checking several cells on the row just to be sure. End users can be fickle fiends!

VBAjedi

 

[scottsek]

I actually use another method to get the last used row:

I'll assume column A also

To get the cell itself (which is how I usually use it):

Set newDataRow = Range("a1&quot.EntireColumn.Cells(Rows.Count).End(xlUp).Offset(1, 0)

but if you want the row just add .Row to the end:

datarow = Range("a1&quot.EntireColumn.Cells(Rows.Count).End(xlUp).Offset(1, 0).Row

MyFlight, hope this helps.

Scott

 

[MyFlight]

Ok,
One more question. Since I am Copying and Pasting to various cells in this row how will I be able to refer to them after finding the Next Blank Row. Example I need to Copy Text from one worksheet (MainCbx) to another worksheet (TypeCbx) in the New Row.

Copy From Paste To
MainCbx Worksheet TypeCbx Worksheet

Cell "C7" New Row Column A (i.e. Cell "A2&quot
B2:B65384 is an Auto Number Cell
Cell "C9" New Row Column A (i.e. Cell "C2&quotCell "C11" New Row Column A (i.e. Cell "D2&quotCell "C13" New Row Column A (i.e. Cell "E2&quotCell "C15" New Row Column A (i.e. Cell "F2&quot

In addition, I will need to maintain this Row Number for use in onther Worksheets within the Workbook.

 

[Loomah]

MyFlight
All of the above will give you the last row that you require and now for some self promotion! There are a couple more solutions in this forum's FAQs, One by me (doesn't care about gaps, empty rows, columns etc!) and the other by xlbo. The end result is the same - you get a row number.

Assign this number to a variable eg using Rob's example assign the numeric value of the row to a variable called "MyRow" ( a word of warning, declare your variable as type Long not Integer. This is because the scope of Integer only goes up to just over 32k and you may be using upto 65536 rows).

To use your variable - the answer is there in Rob's post

Sheets("CBX MAC")[b].cells(MyRow,1)[/b].PasteSpecial xlValues

Your final point may have me stumped! You variable declared publicly (see Rob's post. Again!)will contain the value for the 'life' of your code. It isn't posible to assign values to Constants (as far as I know) so that is out! One way around this is to write the value to a cell somewhere and reference that cell for the value (see below). The last thing to consider, most of these solutions will find your row number pretty quickly. Is there any harm in finding it each time you need it?

Assign val to cell

Worksheets("Conts").Range("A1").Value= Sheets("CBX MAC").range("A2").end(xldown).row+1

Use that value!

MyRow = Worksheets("Conts").Range("A1").Value
Sheets("CBX MAC")[b].cells(MyRow,1)[/b].PasteSpecial xlValues

;-) If a man says something and there are no women there to hear him, is he still wrong?

 

[RobBroekhuis]

As long as you are within the same VBAProject (i.e., the same workbook), the public variable will retain its value. Doesn't matter which worksheet is active.
Rob

 

[MyFlight]

Loomah, Rob, Scott, Jedi;

Thank you very much, I finally got it working. Talk about SLOW to get something.

I really appreciate all of your assistance!!!!!