Selecting all cells based on Col A

drozek

Board Regular
Joined
Aug 3, 2011
Messages
67
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
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
To capture all the rows and columns

Code:
Range("A1").currentregion
 
Upvote 0
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).
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
Try

Code:
    Range("A3", Range("A3").SpecialCells(xlLastCell)).Select
    
    'or
    Range("A3", ActiveSheet.UsedRange).Select
 
Upvote 0
Try

Code:
    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
 
Upvote 0
I'm glad to help you. Thanks for the feedback.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,306
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top