Selecting all cells based on Col A

[drozek]

So I am tried to record a macro, and I am trying to select all the Columns and Rows based on how many rows there are on Column A. The "Range("A3:N100").Select" isn't doing it since it might be more or less, then my pivot tables gives me blanks or 0's that are in some of the cells. Any idea?

Sheets.Add After:=ActiveSheet
Sheets("Sheet4").Select
Sheets("Sheet4").Name = "by Size"
Sheets("by Customer").Select
Range("A3:N100").Select
Range("L3").Activate
Selection.Copy
Sheets("by Size").Select
ActiveSheet.Paste Link:=True


Range("A1").Select
Application.CutCopyMode = False
Application.CutCopyMode = False
Sheets("by Size").Select
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"by Size!R1C1:R100C14", Version:=6).CreatePivotTable TableDestination:= _
"Sheet5!R3C1", TableName:="byCustomer", DefaultVersion:=6

 

[alansidman]

To capture all the rows and columns

Range("A1").currentregion

 

[drozek]

To capture all the rows and columns

Range("A1").currentregion

This currentregion still selected the 0's in M and N Columns. There are no values in Columns A which I want it to base the selection on.

 

[Rick Rothstein]

This currentregion still selected the 0's in M and N Columns.

When in Message #1 did you say the 0's in Columns M and N should not be selected?

There are no values in Columns A which I want it to base the selection on.

Then what does your thread title mean when it says "based on Col A"?


Please Note
-----------------------
One thing you must keep in mind when you ask a question in a forum... the people you are asking to help you know absolutely nothing about your data, absolutely nothing about how it is laid out in the workbook, absolutely nothing about what you want done with it and absolutely nothing about how whatever it is you want done is to be presented back to you as a result... you must be very specific about describing each of these areas, in detail, and you should not assume that we will be able to "figure it out" on our own. Remember, you are asking us for help... so help us to be able to help you by providing the information we need to do so, even if that information seems "obvious" to you (remember, it is only obvious to you because of your familiarity with your data, its layout and the overall objective for it).

 

[drozek]

When in Message #1 did you say the 0's in Columns M and N should not be selected?



Then what does your thread title mean when it says "based on Col A"?

"I am trying to select all the Columns and Rows based on how many rows there are on Column A"


If there is 100 rows in column A, I am trying to figure how to select 100 rows and all the columns N. The number of rows in A can change.

 

[Rick Rothstein]

When in Message #1 did you say the 0's in Columns M and N should not be selected?



Then what does your thread title mean when it says "based on Col A"?

"[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=3]#3[/URL] 33333]I am trying to select all the Columns and Rows based on how many rows there are on Column A"

[/COLOR]
If there is 100 rows in column A, I am trying to figure how to select 100 rows and all the columns N. The number of rows in A can change.

Does "and all the columns N" mean "all the columns from A to N including all columns in between" or "all the columns up to but not including Column N"?

If the first, then there must be some other conditions because you said in Message #3 "This currentregion still selected the 0's in M and N Columns"

Please clarify.

 

[DanteAmor]

Try

    Range("A3", Range("A3").SpecialCells(xlLastCell)).Select
    
    'or
    Range("A3", ActiveSheet.UsedRange).Select

 

[drozek]

Try

    Range("A3", Range("A3").SpecialCells(xlLastCell)).Select
    
    'or
    Range("A3", ActiveSheet.UsedRange).Select

Thanks used Range("A3", ActiveSheet.UsedRange).Select, I did use this in another place, so it wouldn't give me 0's, still very helpful

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.