Help Needed, May have bitten off more than I can chew

[Rick Rothstein]

All I need/want/desire/crave is for a handy tidbit of code that looks

at the History workbook/worksheet and checkout the values in
Column ("A:A") and goto (whether using XlUp or Down) and stop
on the first blank cell and make it the active cell so I can paste in
the range I have selected From the Master.

I have not read the entire thread, so I am going on what you wrote (quoted
above) and on the assumption that the blank cell you are looking to select
is a real blank cell and not one containing a formula that evaluates to the
empty string (""). If that is the case, then try this code...

Worksheets("History").Activate
Columns("A").SpecialCells(xlCellTypeBlanks)(1).Select

I was not entirely sure what you meant by "History workbook/worksheet", so I
assumed it was the name of the worksheet in the active workbook.

Rick Rothstein (MVP - Excel)

 

[Vacuum Sealed]

Thank you for your reply Rick.

Actually, that is kinda where I'm finding it most difficult as any trailing
blank cells in Column A do actually have formulae in them.

I have been toying with the following code:

Which works well if I want to just highlight cells that are not "", but I am
not able to amend it to include the entire row of it selection.

The problem is that once it runs and selects all the cells not Blank, I
can't select the range for copying as excel spits it:

With ActiveSheet
LR = .Range("A" & Rows.Count).End(xlUp).Row
For Each Cell In .Range("A5:R" & LR)
If Cell.Value <> "" Then
If Rng Is Nothing Then
Set Rng = Cell
Else
Set Rng = Union(Rng, Cell)
End If
End If
Next Cell
Rng.Select
End With

TIA
Mick

 

[Vacuum Sealed]

Ooops!!!

Sorry Rick, confusion raining down on you right now I should imagine.

That bit of code was to a seperate one I was toying with to copy the range
from the Master file.

Although, I would like to touch base with you on that if you have time.

The problem is related though, as the current code I used was a standard
range(A5:A200"). which was fine, but when I ran the code again, it placed
the next import of data 60 rows below as, although the amount of data will
rarely extend to row 200, at the time, I was making provisions in that
event, suffice to say, when pasting to the "History" File it takes into
account that the imbedded formula is a not blank cell and stops way down the
sheet.

So to re-cap.

Essentially, using "Master" Column A:A if not blank, select all non blank
rows (Excluding those with formula).copy selection.

Windows("History.xls").Activate

Find the next available blank cell in Column A and
..PasteSpecial.Values......

Clif has been a real sport helping me by making me do the head work and
having me try and contruct it for myself in order to learn how to be self
relient, but the cup is kinda full of Access related coding and I just can't
make the dots connect.

That will enable me to move onto the next phase, which will no doubt bring
with it it's own subset of head smashing agony....LOL....

TIA
Mick

 

[Vacuum Sealed]

Also Rick

I am calling the GetLastRow Function to locate the next available blank cell
in "History.xls"

But...!!!

If you have a better alternative to that, I'm open to it.

Cheers

 

[Clif McIrvin]

What a difference a day can make..!!!!!!!!!

Hi Clif

I'm just about ready to detonate and throw my monitor out the window.
I've spent last night and most of this afternoon leading into the
evening with zero return, apart from the frustration and anxiety
levels going through the roof.

I have read the help files on XlUp Down sideways inside out, you name
it, looked at the examples and still I am no closer to getting this
last hurdle out the way.

All I need/want/desire/crave is for a handy tidbit of code that looks
at the History workbook/worksheet and checkout the values in Column
("A:A") and goto (whether using XlUp or Down) and stop on the first
blank cell and make it the active cell so I can paste in the range I
have selected From the Master.

The rest is cake & cream.

Down on knee's anyone out there know how this can be achieved
please....

Many, many, many thanks way in advance.

Mick

Hoo boy.

From your posts in other branches, I realize that your "blank" cells are
not empty (which was the assumption I was working with) but contain
formulae that are returning the ZLS (zero length string, or "").

Working only with Column A - Assuming:
1. there may or may not be empty cells "below" these formulae
2. there will *always* be at least one "blank" formula cell below your
active data
3. there will *never* be a cell anywhere "above" the desired "first
blank cell" that does not contain a formula (if there is, the code will
need to take Range.Areas into account)

This should select column A:"first blank row":

Dim ca As Range ' Column A
Dim lStart As Long
Dim lEnd As Long
Dim lLoop As Long

' set ca to all cells containing formulas in column A
Set ca = Columns("A").SpecialCells(xlCellTypeFormulas, 23)

lStart = ca.Count ' start at the bottom
lEnd = 1

For lLoop = lStart To lEnd Step -1
If ca(lLoop).Value = "" Then
ca(lLoop).Activate
Exit For
End If
Next lLoop
If lLoop = lEnd - 1 Then
MsgBox "No blank cell found!!!", vbCritical
Stop ' error handling needed here
End If

Set ca = Nothing ' clean up


Rick, thanks for joining the thread -- I believe your contribution got
us off the hold-up!

 

[Clif McIrvin]

Oops -- I was in too big a hurry ... corrected code below.

Hoo boy.

From your posts in other branches, I realize that your "blank" cells
are not empty (which was the assumption I was working with) but
contain formulae that are returning the ZLS (zero length string, or
"").

Working only with Column A - Assuming:
1. there may or may not be empty cells "below" these formulae
2. there will *always* be at least one "blank" formula cell below your
active data
3. there will *never* be a cell anywhere "above" the desired "first
blank cell" that does not contain a formula (if there is, the code
will need to take Range.Areas into account)

This should select column A:"first blank row":

Dim ca As Range ' Column A
Dim lStart As Long
Dim lEnd As Long
Dim lLoop As Long

' set ca to all cells containing formulas in column A
Set ca = Columns("A").SpecialCells(xlCellTypeFormulas, 23)

lStart = ca.Count ' start at the bottom
lEnd = 1

For lLoop = lStart To lEnd Step -1
If ca(lLoop).Value <> "" Then
ca(lLoop + 1).Activate
Exit For
End If
Next lLoop
If lLoop = lEnd - 1 Then
ca(lLoop + 1).Activate
End If

Set ca = Nothing ' clean up

 

[Clif McIrvin]

Witten in haste, reviewed in haste. Re-revised code and comments
in-line.

Hoo boy.

From your posts in other branches, I realize that your "blank" cells
are not empty (which was the assumption I was working with) but
contain formulae that are returning the ZLS (zero length string, or
"").

Working only with Column A - Assuming:
1. there may or may not be empty cells "below" these formulae
2. there will *always* be at least one "blank" formula cell below your
active data

2. the code below does not require "blank" formula cells below the
active data. If the last formula cell is non-blank, the following row
will be chosen.

3. there will *never* be a cell anywhere "above" the desired "first
blank cell" that does not contain a formula (if there is, the code
will need to take Range.Areas into account)

4. This code does not test for possibly exceeding the Excel maximum row
number.

This should select column A:"first blank row":

Dim ca As Range ' Column A
Dim lBottom As Long
Dim lTop As Long
Dim lLoop As Long

' set ca to all cells containing formulas in column A
Set ca = Columns("A").SpecialCells(xlCellTypeFormulas, 23)

lBottom = ca.Count ' start at the bottom
lTop = 1

For lLoop = lBottom To lTop Step -1
If ca(lLoop).Value <> "" Then
Exit For
End If
Next lLoop
ca(lLoop + 1).Activate

Set ca = Nothing ' clean up

 

[Rick Rothstein]

What about this single line of code (it selects the first blank cell in
Column A...

Columns("A").Find("", Cells(Rows.Count, "A"), xlValues, _
xlWhole, , xlNext).Select

If the entire row is to be selected, then use this instead/..

Columns("A").Find("", Cells(Rows.Count, "A"), xlValues, _
xlWhole, , xlNext).EntireRow.Select

Rick Rothstein (MVP - Excel)




"Clif McIrvin" wrote in message

Witten in haste, reviewed in haste. Re-revised code and comments
in-line.

Hoo boy.

From your posts in other branches, I realize that your "blank" cells are
not empty (which was the assumption I was working with) but contain
formulae that are returning the ZLS (zero length string, or "").

Working only with Column A - Assuming:
1. there may or may not be empty cells "below" these formulae
2. there will *always* be at least one "blank" formula cell below your
active data

2. the code below does not require "blank" formula cells below the
active data. If the last formula cell is non-blank, the following row
will be chosen.

3. there will *never* be a cell anywhere "above" the desired "first blank
cell" that does not contain a formula (if there is, the code will need to
take Range.Areas into account)

4. This code does not test for possibly exceeding the Excel maximum row
number.

This should select column A:"first blank row":

Dim ca As Range ' Column A
Dim lBottom As Long
Dim lTop As Long
Dim lLoop As Long

' set ca to all cells containing formulas in column A
Set ca = Columns("A").SpecialCells(xlCellTypeFormulas, 23)

lBottom = ca.Count ' start at the bottom
lTop = 1

For lLoop = lBottom To lTop Step -1
If ca(lLoop).Value <> "" Then
Exit For
End If
Next lLoop
ca(lLoop + 1).Activate

Set ca = Nothing ' clean up

 

[Clif McIrvin]

I was pretty sure there were other ways to approach this question <g>

I've heard it said that when the only tool you know is a hammer, every
problem looks like a nail.

Thanks!

Clif

 

[Vacuum Sealed]

My humblest of apologies to both of you as I seem to have confused each of
you.

Clif

This last very nice code works well at locating a the first blank cell down
column A.

This worked well when I inadvertantly pasted it before the copy range
statement, but in effect it made the first blank cell of "Master.xls" the
ActiveCell which is when I realised I should have pasted it after
"History.xls" was activated.

That said...!!! It hangs on the Set CA section

And this is probably where the confusion has been added by me.

The EntireRow.Select was to select the entire row of all Cells being copied
from the "Master.xls" to "History.xls".

What was happening prior is that when I was calling the GetFirstBlankRow
Function after "History.xls" was activated, I assumed the embedded formulae
was not a blank cell and activated the cell it interpreted as being blank.

Thx again for all your efforts.

Mick

 

[Vacuum Sealed]

Crap, I need to proof read these things before I post em.

Repost:

My humblest of apologies to both of you as I seem to have confused each of
you.

Clif

This last very nice code works well at locating the first blank cell down
column A in "Master.xls".

This worked well when I inadvertantly pasted it before the copy range
statement, but in effect it made the first blank cell of "Master.xls" the
ActiveCell which is when I realised I should have pasted it after
"History.xls" was activated.

And this is probably where the confusion has been added by me.

Rick

The EntireRow.Select was to select the entire row of all Cells being copied
from the "Master.xls" to "History.xls".

So disregard the EntireRow.Select section as it turns out to be irrelavent
now that I can use the SpecialCells Set ca

What was happening prior is that when I was calling the GetFirstBlankRow
Function after "History.xls" was activated, It assumed the embedded formulae
was not a blank cell and activated the cell it interpreted as being blank
which meant it kept activating a cell 60 or so row below the last line of
populated cells.

That said...!!! It hangs on

Set ca = Columns("A").SpecialCells(xlCellTypeFormulas, 23)

Just an after thought:

This Function worked to a degree as I explained, al-be-it the formulae was a
snag

Public Function GetLastRow() As Long


Dim ExcelLastCell As Object, Lrow As Long, lLastDataRow As Long, l As
Long


Set ExcelLastCell = ActiveSheet.Cells.SpecialCells(xlLastCell)

lLastDataRow = ExcelLastCell.Row

Lrow = ExcelLastCell.Row


Do While Application.CountA(ActiveSheet.Rows(Lrow)) = 0 And Lrow <> 1

Lrow = Lrow - 1

Loop


lLastDataRow = Lrow



GetLastRowWithData = lLastDataRow


End Function



Thx again for all your efforts.

Mick

 

[Clif McIrvin]

Comments in-line

Crap, I need to proof read these things before I post em.

Repost:

My humblest of apologies to both of you as I seem to have confused
each of you.

Clif

This last very nice code works well at locating the first blank cell
down column A in "Master.xls".

Did you catch Rick's suggestion of using the Range.Find method instead?
As long as the first "blank" formula cell is the one you are looking
for, the .Find method will be much faster than the For Next loop, and I
would consider it to be much cleaner code. It's pretty obvious that Rick
has much more experience than I do, and I'm glad he joined the thread!

If there can be "blank" cells above the one you want, my code won't ever
find them because it is looking up from the bottom, where Rick's
suggested .Find is looking down from the top. I think the .Find can be
modified to look up from the bottom, by modifying the After:= and
SearchDirection:= parameters.

This worked well when I inadvertantly pasted it before the copy range
statement, but in effect it made the first blank cell of "Master.xls"
the ActiveCell which is when I realised I should have pasted it after
"History.xls" was activated.

When you are working with Active cells and Selections you need to be
*very* careful about what has the focus. After many months of dabbling
with this sort of thing, I realized that using Range objects makes it
much easier for me to explicitly define what range I am operating upon
(the $15 word is disambiguation.) (With ... End With constructs are
another way to work with explicit range objects.) Not only that, but
using range objects and temporarily turning off the user interface runs
a lot faster than using .Select and/or .Activate.

Dim myRange1 as range
Dim myRange2 as range
Dim myRange3 as range

' etc....

set myRange1 = some range of interest
set myRange2 = some other range of interest -- can be on the same or any
other sheet

then you can use myRange1.property or method, etc as self-documentation
depending on what you use as the actual variable names.

I'm not sure how important it is, but I make a practice of always
explicitly releasing objects that I instantiate in code before I exit
the procedure:

set myRange1 = Nothing
etc.

And this is probably where the confusion has been added by me.

Rick

The EntireRow.Select was to select the entire row of all Cells being
copied from the "Master.xls" to "History.xls".

So disregard the EntireRow.Select section as it turns out to be
irrelavent now that I can use the SpecialCells Set ca

What was happening prior is that when I was calling the
GetFirstBlankRow Function after "History.xls" was activated, It
assumed the embedded formulae was not a blank cell and activated the
cell it interpreted as being blank which meant it kept activating a
cell 60 or so row below the last line of populated cells.

That said...!!! It hangs on

Set ca = Columns("A").SpecialCells(xlCellTypeFormulas, 23)

Have you gotten past that error? I'm unclear from your post whether you
have gotten this working or not. If this is giving you problems,
posting the actual text of the error message will be helpful.

In the line above, Columns("A") is referring to the worksheet with the
focus. From the help on Columns: "Returns a Range object that
represents all the columns on the active worksheet. If the active
document isn't a worksheet, the Columns property fails."

Just an after thought:

This Function worked to a degree as I explained, al-be-it the formulae
was a snag

Public Function GetLastRow() As Long


Dim ExcelLastCell As Object, Lrow As Long, lLastDataRow As Long,
l As Long


Set ExcelLastCell = ActiveSheet.Cells.SpecialCells(xlLastCell)

lLastDataRow = ExcelLastCell.Row

Lrow = ExcelLastCell.Row

I "think" that [ Lrow = lLastDataRow ] would execute faster - a simple
assignment instead of a call to a property.

Do While Application.CountA(ActiveSheet.Rows(Lrow)) = 0 And Lrow
<> 1

Lrow = Lrow - 1

Loop


lLastDataRow = Lrow



GetLastRowWithData = lLastDataRow

As presented, I don't see the purpose for lLastDataRow at all - that's
just extra burden to your procedure.

End Function



Thx again for all your efforts.

Mick

Happy to help!

 

[Vacuum Sealed]

Clif / Rick

Neither Set ca = codes worked, although it Compiles fine, just the execution
halts.

Here is the section of code that gives you the before and after bits the it
is dealing with:


On Error Resume Next
Windows("WowSchedMaster.xls").Activate
On Error GoTo 0


'Selects the range to be copied across

Range("A5:R200").Select
Selection.Copy


'Sets the focus back onto WowSchedHistory

On Error Resume Next
Windows("WowSchedHistory - 2011.xls").Activate
On Error GoTo 0


'Locate the first available cell in Column "A"

' Set CA to all cells containing formulas in column A

Set ca = Columns("A").Find("", Cells(Rows.Count, "A"), xlValues, _
xlWhole, , xlNext).Select


lBottom = ca.Count ' start at the bottom
lTop = 1

For lLoop = lBottom To lTop Step -1
If ca(lLoop).Value <> "" Then
Exit For
End If
Next lLoop
ca(lLoop + 1).Activate

Set ca = Nothing ' clean up

'Paste information to first Blank Cell

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False


Thx again
Mick

 

[Clif McIrvin]

The .Select at the end of your Set ca = line is causing your execution
halt. You can either [ Set (some object vairable) = xxx ] or you can
[ xxx.Select ] but you cannot do both at the same time. If you did need
to set the object and Select the range, you would do it on two lines,
thus:

Set ca = xxx
ca.Select

That being said, Rick's one-line solution is much more elegant than my
VBA loop: I "think" all you really need is this single line:

Columns("A").Find("", Cells(Rows.Count, "A"), xlValues, _
xlWhole, , xlNext).Select

Also, I'm nervous about your use of On Error Resume Next without any
error handling. If the Window.Activate fails, your code won't know and
will produce erroneous results. If you're going to use Error Handling,
supply code to deal with potential failures.

Try replacing the code snippet you posted with this one:

Windows("WowSchedMaster.xls").Activate

'Selects the range to be copied across

Range("A5:R200").Select
Selection.Copy

'Sets the focus back onto WowSchedHistory

Windows("WowSchedHistory - 2011.xls").Activate

'Locate the first available cell in Column "A"

Columns("A").Find("", Cells(Rows.Count, "A"), xlValues, _
xlWhole, , xlNext).Select

'Paste information to first Blank Cell

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False


HTH!

Clif

 

[Vacuum Sealed]

It's any wonder you guy's are called Gur's

Thank you so much to both Rick and yourself Clif.

She works sweet as.

Thx again for all your patience and guidance.

Cheers
Mick.

 

[Clif McIrvin]

It's any wonder you guy's are called Gur's

Thank you so much to both Rick and yourself Clif.

She works sweet as.

Thx again for all your patience and guidance.

Cheers
Mick.

Glad you got it working!! Happy to help.

And, once again, Thanks to Rick for stepping in an teaching me, too!