I have problems with hidden cells 1

[Andrea001]

I wrote the following code to get data from one sheet to another. On the destination sheet I want to choose the next available blank cell to put the data into.

Sheets("Newdata&quot.Select
Range("A1&quot.Select
Cells.Find(What:="The Data.", _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False).Activate
Set myrange = ActiveCell
myrange.Offset(1, 1).Select
Set myrange = ActiveCell
myrange.Resize(2, 1).Select
Selection.Copy

Sheets("Net FFS&quot.Select
Range("A3&quot.Select
Do Until ActiveCell.Text = ""
Set myrange = ActiveCell
myrange.Offset(0, 1).Select
Loop

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

This works great until I hide the first few columns that already have data in. When the columns are hidden, the data gets put into the first available cell, not the first available blank cell. Why would that happen?? (and how can I stop it?)
Thanks,
Andrea

 

[SkipVought]

Hi,
Here's how to select the first blank cell in col A...

    With [A1].CurrentRegion
        .Cells(.Rows.Count + .Row, 1).Select
    End With

Hope this helps Skip,
metzgsk@voughtaircraft.com

 

[Andrea001]

Skip,
If I'm searching for the next blank cell in row 6, would I just replace A1 with A6 and replace Row(s) with Column(s)? I don't understand how it knows that a blank has been encountered. Sorry, don't mean to sound ignorant but I'm fairly new at this VBA stuff ...
Thanks,
Andrea

 

[DaleWatson123321]

Andrea,

When I see an objective such as the one you describe - wherein you are selectively choosing data from one sheet to add to another, I feel the need to point out one of Excel's "most powerful" and also "most overlooked" functions - Excel's "database functionality".

This functionality is related to Data - Filter - Advanced Filter. Many Excel users, however, ONLY get as far as "Filter the list, in-place" - as opposed to using the other option of "Copy to another location".

One of the "key" reasons for this, is due to Excel having a "bug" that triggers a FALSE error message when the user attempts to "Copy to another location" that is on a separate worksheet. It informs the user that it is NOT possible to use this method to copy data to another worksheet.

This is NOT the case. It IS possible to use this method to selectively extract data, based on "criteria" that you specify, and the data CAN be extracted to a separate sheet.

With proper coding, this extracted data can ALSO be "appended" to existing data on the other sheet.

The "criteria" can be as "specific" as you need, to extract ONLY the data you want to "isolate".

Another important component of this "database functionality", is the "database formula" (function). These include: DSUM, DCOUNT, DCOUNTA, DAVERAGE, DGET, DMAX, DMIN, DPRODUCT, DSTDEV, DSTDEVP, DVAR, and DVARP.

The database formula is made up of 3 components:

1) the "database" - a reference to the range containing your data.

2) the "offset" - a reference to the field (in your database) from which you want to have the database function "add" in the case of DSUM for example.

3) the "criteria" - a reference to separate cells on your worksheet where you place: a) the "field names" (copied from the top row of your database), and b) on the 2nd row (beneath each field name), the “criteria” (or type of data you wish to “isolate”).

You don't need to copy all of the field names - ONLY the ones you require in order to "isolate" the data you wish to "extract" - or to "add" in the case of DSUM, or “count” in the case of DCOUNTA, etc. Using these functions, you could produce a “matrix” or summary of data, for example, showing the Totals Sales by Sales Person on a Weekly, Monthly, and/or Yearly basis.

Here’s an example of criteria that would be “dynamic” – i.e. allow the user to specify the “last X number of days” for the formula (and the extraction routine) to search back from today’s date...

If your database includes a “SalesDate” field, you would use a formula such as:
=AND(SalesDate>=last_Xdays,SalesDate<=thisday).

In a separate cell named “last_Xdays”, you would have this formula: =TODAY()-days.

“days”, in turn, is a separate cell (named “days”) where the user can enter the number of days.

In the cell named “thisday”, you would have the formula =TODAY().

This is of course only one example. The criteria can be as specific and/or as complex as you require.

The “same” criteria used for the formulas, can ALSO be used for “extracting” the “records behind each formula” – to a separate sheet – which can be “pre-set” (formatting, etc) for “automatic report generation”, or for “analysis” of the specific data relating to a specific total that is based on “specific criteria”.

Excel does have “quirks” when it comes to creating “criteria”. For example, if your criteria has a formula, then the first row (where the field name normally would be) CANNOT contain the field name. It MUST be blank. However, if your criteria is a LABEL, then you MUST include the field name.

I hope this has been somewhat informative. There is more to learn, but hopefully this should be enough info to “get you started”. The time and effort spent in learning and using these “database functions” will be well worth it.

If you run into any difficulties, I’m available for any specific questions on this particular topic. Normally I’m available at work at the address below, but I’ll be off on vacation next week.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[SkipVought]

Assuming that you want to find the FIRST blank cell in column A and you only have data in column A, my code states...

Starting with Col A, Row 1, using the Current Region (see help for definition), the next empty cell is the Current Region Row Count + 1 (I actually had the Current Region Row Count + the First Row -- generally not correct, but in this case it is)

A better method might be...

[A1].End(xlDown).Offset(1,0).Select

This can also be done on the sheet starting at col A row 1 with the following key strokes...
{END}{DOWN ARROW}{DOWN ARROW}

Hope this helps
Skip,
metzgsk@voughtaircraft.com

 

[Andrea001]

Skip,
Thanks for you help, this makes more sense to me!!
Andrea

 

[SkipVought]

Read Help. Try to gain an understanding of the Object model of Excel. The Range Object (one or more cells) has a powerful set of Properties and Methods associated with it. It is a large set of Properties and Methods. The more you get familiar with them and how to use them, the better you will be able to manipulate spreadsheets with VBA code. Skip,
metzgsk@voughtaircraft.com