Macro treats appearantly "blank cells" as "not blank"

[MarkBeck]

Hi

The code below takes data from individual work cards (i.e., sheets("AD") below), and transfers it to a "central" sheet. Although the entire card is copied and pasted [Range("C1639")], not all lines are actually populated. The next paste looks for the first blank line in the "central" sheet [End(xlDown)], and pastes new data there.

However, even if only three lines were populated, the next paste will start at line 24! It seems that the lines in column "A" are not "really blank". This creates many blank lines in the central sheet.

Also note, that I included a ['] at my MsgBox, since I keep getting a "compile error, expected value =". I want to give the people the chance NOT to proceed with the transfer.

Here is my code;

Sub TransferData()

' msgbox("This action cannot be undone, and will transfer your data and blank your sheet.",vbOKCancel,"data transfer")
ActiveSheet.Unprotect
Sheets("Central").Visible = xlSheetVisible
Range("C1639").Select
Selection.Copy
Sheets("Central").Activate
ActiveSheet.Range("A1").Select
Selection.End(xlDown).Select
Selection.Offset(1, 0).Select
Selection.PasteSpecial
Paste:=xlPasteValues,Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Central").Visible = xlSheetVeryHidden
Sheets("AD").Select
Range("F13").ClearContents
Range("D16:F39").ClearContents
Range("J1639").ClearContents
Range("E13").Select
ActiveSheet.Protect
MsgBox ("Please select week from drop-down list!")
End Sub

 

[Loomah]

never really understood why this happens enough to give an authoratative explanation but....

have a look in the FAQs of this forum - there are at least 2 (one from myself the other from xlbo) that give solutions for finding the last row (or column) containing data which will help work around your problem.

there's also a faq somewhere, i seem to remember, from tony jollans (more of a paper really!!) explaining the differences between empty, blank, null etc which might be of some help

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[SkipVought]

Hopefully, your data is contiguous. Rather than copying a fixed range...

    Range("C16").CurrentRegion.Copy

    with Sheets("Central").Range("A1")
      if .CurrentRegion.Rows.Count>1 then
        .End(xlDown).Offset(1, 0).PasteSpecial xlPasteAll
      else
        .Offset(1, 0).PasteSpecial xlPasteAll
      end if
    End with

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Loomah]

just a couple of random thoughts
are there any formulas in the data you are copying?
actually that's only one thought, rather than a couple!

on the msgbox issue, if you put the parameters within bracket then vb expects you to assign the value returned from the msgbox to a variable. if it's simply a message then drop the brackets
ie
MsgBox "Please select week from drop-down list!"
rather than
MsgBox ("Please select week from drop-down list!")

or MyVar = MsgBox ("Please select week from drop-down list!")


;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[SkipVought]

are there any formulas in the data you are copying?

Actually, even if a formula returns "" (zero length string), there IS something in that cell both when you copy and paste.

If that's the case, is there any way to eliminate those formulas that have no apparent value? Check out the Data > Create > List... feature.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[MarkBeck]

Thanks Skip.

My problem with "CurrentRegion" is that column "C" has a formula that spans "C16:C39", and there are some Footer notes too. So it copies a larger area then is required in the "Central sheet".

But if D:F are empty, "C" returns [""]!

(And "D" is a reliable column to test for the region height I want to copy)

Already my code is neater due to your tip. Can you tweak your suggestion to ignore the [""] in "C"? (as well as footer)?

Relevant code as it stands;

Range("C16").CurrentRegion.Offset(1, 0).Copy

Thanks

 

[MarkBeck]

Thanks Loomah

Re MsgBox; I took the parenthesis out, but then "cancel" doesn’t cancel the action. It seems that I to evaluate the answer. What do I do?

 

[MarkBeck]

Skip

>Data > Create > List... feature

Saw this after my posting.

Where do I find that? (In Excel, "Data" is not sub-menu'ed by "Create", and in VBA I don’t see "data" at all?

 

[SkipVought]

"But if D:F are empty, "C" returns [""]!"

The beauty of a 'List' is that your formulas are only added to a row as you enter data in that row.

You might also consider showing your 'footer' data ABOVE your variable rows table containing entered data and formulas -- a STANDARD practice in my book.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[GVF]

If Msgbox("This action cannot be undone, and will transfer your data and blank your sheet.",vbOKCancel+VBCritical,"Data Transfer") = VBCancel then Exit Sub

 

[MarkBeck]

Thanks GVF

Thanks Skip. I think i understand, but will verify

 

[SkipVought]

I set up a small test with 3 columns of data in a table.

The first column is a calculation of the product of column 2 and column 3 in that row.

With the table constructed AND the formula in row 2 column 1, I defined the table as a 'List' using Data > Create > List...

Tab from column 1 to column 2; key in number, tab to column 3; key in number.

Tab again to column 1, row 2, tab to column 2 and key in number; TAB. At this instant, the formula in row 2 appears in row 2 column 1!

Notice, that you can simply key in values and tab. Tab will keep you within the table. Enter will work, but you must reposition the active cell in order to enter the next piece of data.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]