UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> VBA For Selecting The Last Column Of The Excel Worksheet, Any Version    
 
   
Frank Situmorang
post Jul 9 2017, 01:24 AM
Post#1



Posts: 1,203
Joined: 10-June 10



Hello,

This is my VBA:
Sheets("DonorDetail").Select
Range("D3").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range("D3:I12").Select

The non blank Cells is in H12, since the worksheet is dynamic, means some times I12 is non blank, so I tried to do again Select.end(xlToRight), but it can only show Range("D3:I12").Select.

My question, how can we make selection to the very right ( last column) of the Worksheet. What is VBA for it.

Thank you for any help.

Frank
Go to the top of the page
 
GroverParkGeorge
post Jul 9 2017, 07:22 AM
Post#2


UA Admin
Posts: 35,306
Joined: 20-June 02
From: Newcastle, WA


Hm. No matter WHICH columns are populated, that code will always select the same range because the last line is:

Range("D3:I12").Select

Why is it included?
Go to the top of the page
 
Frank Situmorang
post Jul 9 2017, 10:19 AM
Post#3



Posts: 1,203
Joined: 10-June 10



Hi George,

I did not intend to select it, the macro recording itself did it for me, that is why I want to ask in this forum, how can I make it select to column I, or to the very last right column by using select End to the right, coz all the right is blank cells

Thank you for your help

Frank
Go to the top of the page
 
GroverParkGeorge
post Jul 9 2017, 10:20 AM
Post#4


UA Admin
Posts: 35,306
Joined: 20-June 02
From: Newcastle, WA


Just delete that line then. Isn't that possible?
Go to the top of the page
 
Frank Situmorang
post Jul 9 2017, 10:43 AM
Post#5



Posts: 1,203
Joined: 10-June 10



I have deleted it, but it does not copy data in cells column I.

My problem is after Range(Selection, Selection.End(xlDown)).Select, I wanted it to select to column I, but since the last row of column I is blank, it only went to column H. I will let you know, that the row wise in column I is blank, Non blank, blank, and the last is blank. but some times non blank, it depends on the data that I export.

Thank you for your help again.

Frank
Go to the top of the page
 
GroverParkGeorge
post Jul 9 2017, 11:03 AM
Post#6


UA Admin
Posts: 35,306
Joined: 20-June 02
From: Newcastle, WA


Got it. Sorry, I missed that problem.

See if the method described here can work for you.
Go to the top of the page
 
Frank Situmorang
post Jul 9 2017, 11:06 AM
Post#7



Posts: 1,203
Joined: 10-June 10



George,

This is the sample of the range which is dynamic, depending on the imported excel

Attached is the excel file

Thank you for your help.


Frank
Attached File(s)
Attached File  variable_range.zip ( 7.49K )Number of downloads: 3
 
Go to the top of the page
 
GroverParkGeorge
post Jul 9 2017, 11:17 AM
Post#8


UA Admin
Posts: 35,306
Joined: 20-June 02
From: Newcastle, WA


This line should select a range including ALL cells on the worksheet which have a value in them. I.e. your entire range, whether there are blank rows or columns in that range.


ActiveSheet.UsedRange.Select
Go to the top of the page
 
Frank Situmorang
post Jul 9 2017, 11:28 AM
Post#9



Posts: 1,203
Joined: 10-June 10



Thank George,

Since this Range(Selection, Selection.End(xlDown)).Select, is already ok, because I don't want to copy the line total, can I make it

Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.UsedRange.Select

Thanks for your help

Frank
Go to the top of the page
 
GroverParkGeorge
post Jul 9 2017, 11:42 AM
Post#10


UA Admin
Posts: 35,306
Joined: 20-June 02
From: Newcastle, WA


Try it on your data. What does it do for you?
Go to the top of the page
 
Frank Situmorang
post Jul 9 2017, 11:58 AM
Post#11



Posts: 1,203
Joined: 10-June 10



It copys the data that I don't want to copy

Is there any other way?

Thanks

Frank
Go to the top of the page
 
GroverParkGeorge
post Jul 9 2017, 12:05 PM
Post#12


UA Admin
Posts: 35,306
Joined: 20-June 02
From: Newcastle, WA


Okay.

You'll have to identify some parameters that define the Upper left cell and the lower right cell you do want to include. Then use those parameters to define the range.
Go to the top of the page
 
Frank Situmorang
post Jul 9 2017, 12:11 PM
Post#13



Posts: 1,203
Joined: 10-June 10



The upper left is A3, then I used End(xlDown, to get to the lower row, then I want to select to lower right to Column I, what is the VBA to make it

Thank you

Frank
Go to the top of the page
 
Frank Situmorang
post Jul 9 2017, 12:16 PM
Post#14



Posts: 1,203
Joined: 10-June 10



Or you can see this actual VBA

Sheets("DonorDetail").Select
Range("D3").Select
Range(Selection, Selection.End(xlDown)).Select
what is the next VBA statement to make it go from the lower row as the result of the End(xlDown) to column I of the same row number of the last lower row.

Thanks

Frank
Go to the top of the page
 
Frank Situmorang
post Jul 9 2017, 02:50 PM
Post#15



Posts: 1,203
Joined: 10-June 10



George, This is my whole macro:
Sub Frank()
'
' Frank Macro
'

'
Sheets("summary").Select
Range("A3").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.ClearContents
Sheets("Donor").Select
Range("B2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("summary").Select
Range("A3").Select
ActiveSheet.Paste
Sheets("Interface").Select
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.ClearContents
Sheets("DonorDetail").Select
Range("D3").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("Interface").Select
Range("A2").Select
ActiveSheet.Paste
Sheets("Receipt").Select
Application.CutCopyMode = False
End Sub

In this step

Sheets("DonorDetail").Select
Range("D3").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select ---> in this step it only select upto column H ( the last unempty cell) I want it to copy upto column I, even though it is empty, but the cell above it is unempty.

Anybody can help me what is the VBA for it.

Thanks for any help.

Frank
Go to the top of the page
 
GroverParkGeorge
post Jul 9 2017, 05:04 PM
Post#16


UA Admin
Posts: 35,306
Joined: 20-June 02
From: Newcastle, WA


I guess I need better clarity on what you want.

You said, "It copys the data that I don't want to copy"

But you want to include all rows and columns, including blank columns within the populated range.

How do you decide what NOT to copy?
Go to the top of the page
 
larai
post Jul 10 2017, 12:57 PM
Post#17



Posts: 1,114
Joined: 8-February 02
From: California, USA


This will copy the range on DonorDetail and paste it on the interface worksheet. You don't need to select anything.

CODE
    Dim wsDonorDetail As Worksheet
    Dim wsInterface As Worksheet
    
    Set wsDonorDetail = Sheets("DonorDetail")
    Set wsInterface = Sheets("Interface")
    wsDonorDetail.[d3].Resize(wsDonorDetail.[d3].End(xlDown), 6).Copy Destination:=wsInterface.[a2]
Go to the top of the page
 
Frank Situmorang
post Jul 10 2017, 02:25 PM
Post#18



Posts: 1,203
Joined: 10-June 10



Sorry George, probably it is because hard to explain it in English for me as a new immigrant non English speaker, but let me explain it in other way.
This is the code to copy the donor detail to interface sheet:

CODE
Sheets("DonorDetail").Select
    Range("D3").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Sheets("Interface").Select
    Range("A2").Select
    ActiveSheet.Paste


The code will perfectly copy if the data is like this:
Attached File  Imported_2.jpg ( 176.34K )Number of downloads: 3


The code:
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select

will perfectly work because column I3 is not empty ( click the picture in order to show it bigger)

But if the data is like this the selected data copied is only upto column H, because column I3 is empty
Attached File  imported_1.jpg ( 126.29K )Number of downloads: 2


That is why my question is what is the code in order to always copy upto Column I when it does "Range(Selection, Selection.End(xlDown)).Select"

Your help is very appreciated..

Frank
Go to the top of the page
 
Frank Situmorang
post Jul 10 2017, 02:28 PM
Post#19



Posts: 1,203
Joined: 10-June 10



Larai,

Thank you for your help, but this is not just to copy all, but to copy certain portion of the data, I do not want the total and others.
Pls. see my response to George.

Thank you,

Frank
Go to the top of the page
 
larai
post Jul 10 2017, 03:36 PM
Post#20



Posts: 1,114
Joined: 8-February 02
From: California, USA


Did you try using the code I provided?

Because it first sets the rows to [d3].end(xldown), and then over to column I. When I use test data, it copies and pastes correctly.

BR,
Lillianne
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search


RSSSearch   Top   Lo-Fi    18th July 2019 - 04:48 PM