Select a range based on the last usable data row/cell in table

[jnameika]

I have an ugly spreadsheet that I inherited and want to select a range of cells in a table to copy (and send out in an email) based on finding the last row of usable data in the table. The table is fixed:6 columns x 154 rows RANGE(A1:F154). Data is copied and pasted into this fixed range regardless of the number of actual needed rows. Unused cells contain "," & "-" (see Before Paste table below).

Here is the format of the cells prior to paste of data (each character represents a column (R154xC5)):

Before Paste:

A B C D E F
(R1) , - - - - -
(R2) , - - - - -
(R3) , - - - - -
(R4) , - - - - -
ETC...

After Paste:
A B C D E F
(R1) Doe, John 056 123 546 345 999
(R2) Smith, Sarah 000 232 568 968 222
(R3) Jones, David 222 365 768 246 123 <- last entry
(R4) , - - - - -
(R5) , - - - - -
Etc...

In the After Paste table, my last row of data is row 3 (Jones, David...), therefore the needed range is (A1:F3) NOT (A1:F154). This varies depending on the number of records received from hour to hour. I have started to work on a completely new approach in my "spare" time, but in the interim, I need to do this.

I have found some procedures that are close, but not quite. I was thinking MAYBE vlookup. But the logic fails me. I appreciate and value those opinions of others with so much more experience than me. I know there is code to do this, but don't have the experience to figure it out.

Thanks! Jimi

 

[SkipVought]

Hi,

It seems that what you are asking is to copy the used range from SheetA and paste it into SheetB!A1.

Sheets("YourSheetA").[A1].CurrentRegion.Copy _
  Destination:= Sheets("YourSheetB").[A1]

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[jnameika]

Thank you, but what you provided copies the whole range A1:F154. That's the problem, I don't want the whole range of the table. There are no blank cells in the whole table as shown in my examples. Cells in column A (A1:A154) contain commas (,) and cells in columns B - F contain dashes (-), due to =IF(ISERROR({cell}+{cell}),"-",({cell}+{cell})). Column A has employee names in text copied into the cells. The Cells in columns B - F display results of a formula or a dash. I don't want any rows below the last employee entry. Meaning, I don't want records with commas and dashes as they are invalid (empty of data). The valid range of cells span where the data ends and the commas and dashes begin.

I basically want the macro to select only those rows that contain valid data records for me so that I may right click on the selected range and then copy it into an Outlook email. Just a time saver to me and other users until I convert it to Access. The fewer steps, the fewer errors, and less time involved in running the report. I am all about saving keystrokes and eliminating steps.


Thanks, Jimi

 

[SkipVought]

Use the Autofilter to select cells that do not equal [COMMA]values.

Turn on your macro recorder to record setting the criteria.

Then the COPY code is...

Sheets("YourSheetA").[A1].CurrentRegion.SpecialCells(xlCellTypeVisible).Copy _
  Destination:= Sheets("YourSheetB").[A1]

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]