visual basic in excel -

Hi -  I would like to Copy/Paste an area dependant on how many rows there are 
on a spreadsheet. I was thinking of using something like the below:

X = COUNT("D:D")  

Range("A1:A" & X).Select
Selection.Copy
Range("D1:D" & X).Select
ActiveSheet.Paste
Range("E4:E" & X).Select

Setting X as the COUNT of Column D, then adding '&' X to the Range to set 
the length of the Copy/Paste. 
I haven't gotten it to work.  Any advice?

Thanks!
0
DKehl (1)
3/23/2005 6:15:05 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
573 Views

Similar Articles

[PageSpeed] 58

X = Application.COUNT("D:D")

you might want COUNTA if there is text in the column

-- 

HTH

RP
(remove nothere from the email address if mailing direct)


"DKehl" <DKehl@discussions.microsoft.com> wrote in message
news:4CF1B33A-FF21-4B8F-B0DF-F41342C7ADF5@microsoft.com...
> Hi -  I would like to Copy/Paste an area dependant on how many rows there
are
> on a spreadsheet. I was thinking of using something like the below:
>
> X = COUNT("D:D")
>
> Range("A1:A" & X).Select
> Selection.Copy
> Range("D1:D" & X).Select
> ActiveSheet.Paste
> Range("E4:E" & X).Select
>
> Setting X as the COUNT of Column D, then adding '&' X to the Range to set
> the length of the Copy/Paste.
> I haven't gotten it to work.  Any advice?
>
> Thanks!


0
bob.phillips1 (6510)
3/23/2005 7:14:50 PM
What hasn't worked?

One alternative:

   Range("A1:A" & Range("D" & Rows.Count).End(xlUp).Row).Copy _
             Destination:=Range("D1")

Note that no selections are needed.



In article <4CF1B33A-FF21-4B8F-B0DF-F41342C7ADF5@microsoft.com>,
 "DKehl" <DKehl@discussions.microsoft.com> wrote:

> Hi -  I would like to Copy/Paste an area dependant on how many rows there are 
> on a spreadsheet. I was thinking of using something like the below:
> 
> X = COUNT("D:D")  
> 
> Range("A1:A" & X).Select
> Selection.Copy
> Range("D1:D" & X).Select
> ActiveSheet.Paste
> Range("E4:E" & X).Select
> 
> Setting X as the COUNT of Column D, then adding '&' X to the Range to set 
> the length of the Copy/Paste. 
> I haven't gotten it to work.  Any advice?
0
jemcgimpsey (6723)
3/23/2005 7:23:53 PM
Reply:

Similar Artilces:

Using Visual C++ 6.0 MFC Application
01/19/2004 Using a single document or a dialog application, I am able to use my own variables that are declared in the same source file, however, if I try to declare a global variable in a header file or a source file included before the code I am using, I get the error "Undeclared Identifier". Example in Old C: "First.h" int i; "Main.cpp" #include "First.h" main() { i=5; printf("%d",i); } When I declare a variable in a header file, in Class View - Globals, my variable does show up, b...

Regular expressions in Excel
Hello all, I would like to know if it is possible to handle regular expressions in Excel, in VB or in formulas. I would like to do things such as "if cells(x,y)=<regexp> then ..." Is that possible ? Thanks in advance. You can use Regular Expressions if you use VBScript.RegExp, creating a function that you can use. Here is an example that validates email addresses. '----------------------------------------------------------------- Public Function ValidEmail(Adress As String) As Boolean '----------------------------------------------------------------- Dim oRegEx...

how do I insert a watermark onto an Excel spreadsheet?
I am able to insert a watermark onto a Word Document, but cant seem to do the same for Excel. Is there a way? Hi Anna, Watermark, printing of, Robert Rosenberg. Also Ron de Bruin has a zip file. http://google.com/groups?selm=%23Af%244ZZeAHA.1692%40tkmsftngp02 http://www.rondebruin.nl/files/Watermark.zip If you don't care about printing do a search on Watermark in your Excel HELP. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/exce...

Question about excel 2007
Hi, I just switched to excel 2007. On my old excel 2003 toolbar I had a box that showed the location of the file I was working on. It was really helpful if I wanted to send someone the link to my work, I could just copy and paste it from there. I can't find that in the toolbar options of excel 2007. Do you know what it's called? Does it even exist? Thanks. Lisa Add to the QAT(quick access toolbar) the "Document Location" button. (Find it in Commands not in the Ribbon) lj wrote: > Hi, I just switched to excel 2007. On my old excel 2003 toolbar I had > a box t...

Excel VBA
I can't seem to find the 'Draw" menu in Excel VBA... Am I correct in assuming that there is none? And that the Frame is the only way to group stuff on a Form?? thanks The frame is typical, but you can group some controls by using the GroupName. If you want lines on the userform, I think lots of people use a label that's really skinny--or use a picture of a line and then use that picture. tmb wrote: > > I can't seem to find the 'Draw" menu in Excel VBA... > > Am I correct in assuming that there is none? > > And that the Frame is the onl...

How to copy mails to Excel
Hi, I will be sending queires through outlook and i will get the replies for the queries from the same day /week / month. I want to automate these query mails and their respective replies in Ms-Excel. To be precise Mails send and receipts in Outlook with its contents has to be updated in Ms-Excel Hi Saravanan, Cut and paste this code to excel module and run see if this helps. Sub EMailActiveWorksheet() Dim OL As Object ' Outlook instantiation Dim EmailItem As Object ' A new mail item (e-mail) Dim FileName As String ' The name of the file we are attaching ...

Erasing Outlook Calendar from within Excel
I have several schedules in different Excel files that I need to export I need to run the exporter from each file everyday to keep my Calenda up to date. How can I have the macro in each Excel file erase all item from the Outlook Calendar with a given address before it exports m dates? In other words: I run the Macro; it scans the outlook calenda and erases all entries in my Outlook Calendar with an address of "12 Anywhere St"; then it scans my list of dates and copies them from Exce to my Outlook Calendar. I already have the part written that scans m list of dates and copies them t...

basic example with sockets
Does someone know where I can find a very simple example of internet programming using sockets, where you create a server and a client, connect to the server with the client and then exchange a few messages and then close down? http://tangentsoft.net/wskfaq/ Sreeram Joachim wrote: > Does someone know where I can find a very simple example > of internet programming using sockets, where you create a > server and a client, connect to the server with the client > and then exchange a few messages and then close down? ...

help with excel #2
How do i change the layout of a sheet. You can change some settings by choosing File>Page Setup. For specific help, you could describe how you'd like to change the layout. lhws wrote: > How do i change the layout of a sheet. -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html That's a pretty generic question. More details would seem to be in order. Gord Dibben Excel MVP On Mon, 27 Sep 2004 14:57:02 -0700, "lhws" <lhws@discussions.microsoft.com> wrote: >How do i change the layout of a sheet. you might start ...

Excel Automation, Cell Border Lines
I am trying to figure out how to put border lines around each cell in my excel data. I am using automation from access but the code I am using is only putting a border around everything instead of putting the border around each cell. Below is the code I have been trying to get to work. ********************************************* Dim x, y As Long x = 4 'initial row# y = 1 'initial column# ExSheet.Range(ExSheet.cells(x, y), ExSheet.cells(x + 2, y + 2)).Borders(7).Weight = 2 ExSheet.Ran...

How to restore the line of: File, Edit, View, in excel
My excel 2003 now does not show the top line with the options: File, edit, View, Insert, Format, Tools, Chart, Windows, Help Dont find how to retore it, if press F11, they appear in a chart that automatically is add to workbook, but other sheets will not show it. Without this cannot use excel. Please advise how to restore it. alt/t>customize>toolbars>check worksheet menu bar> -- Don Guillett SalesAid Software dguillett1@austin.rr.com "RPM55" <RPM55@discussions.microsoft.com> wrote in message news:CF511981-107E-4B50-A06B-0A7F6ACC9606@microsoft.com... > My ...

Validation in Excel #2
Thanks Myrna ! -- susilo_saja ------------------------------------------------------------------------ susilo_saja's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=15914 View this thread: http://www.excelforum.com/showthread.php?threadid=273937 ...

Standard Visual Basic vs Visual Basic for Applications
I've recently purchased Front Page and understand that I can code with Visual Basic as part of this software package. 1. What does Front Page contain: Standard Visual Basic or VBA? 2. Do either or both work with Visual Studio? 3. Is Visio different from Visual Studio? -- Deb Front Page and Visio are both Microsoft Office applications that contain VBA (Visual Basic for Applications). These programs are productivity applications first and programming platforms second. Visual Studio is a pure programming application. (All it does is let you write other programs.) Older versions of ...

Sort order : Excel vs imported data
It would appear that the sort order of items returned via a Data Query is different to that obtained following a sort within Excel. As an example, the imported list gives a sort order of FS-BZ followed by FSBM, whereas the Excel sort gives an order of FSBM followed by FS-BZ. Is there a global parameter that can be set to include, or not, characters such as "-" in the sort ? Or do I have to create a macro to ensure all data is sorted within Excel before doing a calculation. -- John Reynolds ...

very basic MS access 2007 (button click no read issue)
I added 2 text fields and a button to a form in MS access 2007. and i went to the code builder and tried to add values to the text fields . ex: textBox1.text = "name" i coded this to the button click event.. but some times after i get an error.. the button click property never works... so i have to create a new form and redo it. can some one tell me why this is hapenning , and tell me a way to prevent this.. In Access, you can only use the text property if the control has the focus. Instead, try: textbox1 = "Name" or textbox1.Value = "...

using Excel 2007 student with Excel 2007 commercial
At home I have Excel 2007 student version. My boss has Excel 2007 commercial version. Can I work at home and transfer files to my boss's computer? My boss's computer would not open my work. Any ideas and help is appreciated. Thanks! You should be able to share files. How did the boss try to open the file -- by double clicking on it in windows explorer (or opening from an email) or by starting excel and then using the equivalent of File|open? And what happened when the boss tried and failed? Any error/warning messages? Suzie762 wrote: > > At home I ha...

Cell text limitations in Excel 97
Has anyone encountered text limitations in a cell in Excel 97? The formatting is minimal: text is top aligned and the 'wrap text' is engaged. It wraps until a certain point and then no longer. Even when the width of the cell is widened, text is still hidden. Text can be viewed in the formula bar when single- clicking the cell. But is partially hidden from view in Normal view and when printed. I saw in the Knowledge Base that text is truncated to 255 characters or less if the text is 'justified' but this is merely top-aligned. Appreciate any assistance! Laura &...

Excel Lotus equivalent
Is there an equivalent of Lotus DSUM when exporting lotus file to excel? I would have thought Excel's DSUM = Lotus' DSUM -- Bernard Liengme www.stfx.ca/people/bliengme remove CAPS in email address "darryl" <dmck2762@bigpond.net.au> wrote in message news:MXEgd.3467$K7.1647@news-server.bigpond.net.au... > Is there an equivalent of Lotus DSUM when exporting lotus file to excel? > On Sat, 30 Oct 2004 09:08:10 -0300, "Bernard Liengme" <bliengme@stfx.TRUENORTH.ca> wrote: >I would have thought Excel's DSUM = Lotus' DSUM Not quite...

Opening an Excel attachement from Outlook
When opening as excel attachement in Excel and we try to save the file,it defaults to a temp folder. Is there a way to change this setting to default to the my documents folder for the user? If you do a File|Save, excel saves to the same location. In xl2k and higher, file|saveAs tries to save to the same location as the workbook that was opened. In xl97, file|saveAs tried to go back to your default directory. I find that it's easiest to let excel do what it wants. But in xl2002, you can add your favorite folders to the "save in" (left hand bar in the file|saveAs dialog) ...

How do I add a secondary x axis to an excel 2002 chart
I need to show 2 value sets on different scales which vary with depth in the ground. Naturally the depth should be shown on the y axis and the values shown on the x axis. I therefore need to be able to add a secondary x axis. Can anybody help please. Right-click on one of the series, and select Format Data Series Select the Axis tab Select Secondary Axis, click OK max the driller wrote: > I need to show 2 value sets on different scales which vary with depth in the > ground. Naturally the depth should be shown on the y axis and the values > shown on the x axis. I therefore need ...

excel #90
I want to undo all changes made to a work sheet, the undo button will not undo them all. I WOULD LIKE TO GET BACK TO THE ORIGINAL DOCUMENT THANKS Hi you probably saved the file between your actions. No chance to Undo the changes then! -- Regards Frank Kabel Frankfurt, Germany "Ray" <anonymous@discussions.microsoft.com> schrieb im Newsbeitrag news:063d01c4ad22$3135a8b0$a501280a@phx.gbl... > I want to undo all changes made to a work sheet, the undo > button will not undo them all. I WOULD LIKE TO GET BACK TO > THE ORIGINAL DOCUMENT > > THANKS If you saved ...

Microsoft Excel #8
I work with spreadsheets and consider myself fairly knowledgeable in working with Excel. There is a function that would make data entry much easier for me since I enter data by touch. For example, when I enter data on a spreadsheet and reach the end of the row then I'm ready to start at the next row in the first column. As of right now I am using my arrow keys to go down to the next row and go all the way over to the beginning of the next row. Most of the data that I enter are numbers so therefore I only use one hand 99.9% of the time. I would like to find out if there is a way of h...

I Visual Basic Error "File Not Found" when Excel opens
Good afternoon. I am using XP Pro at work and My computer just started doing this. when I open Excel, I get a message pop up that says in title "Visual Basic Error" and in body of error window is "File Not Found". If I click on help, says something about error 53 but when I try to look that up on Microsoft's website, can't find what seems to apply to my issue. And now when I go into my personal.xls to view my macros, I can get in, but if I try to save anything, I get a window pop up saying Excel must be shut down, and then a window pops up asking me if I want...

C-DLL-VBA-EXCEL strings
Hello, I'm trying to connect excel to a C dll library (call C dll from excel through the VBA). It works well for returning integer and double values (see simple example below), but I can't seem to get it to return strings. I am using MinGW gcc (so basically only C) to construct my dll, so I don't have access to BSTR and other cpp like objects/functions... Is there a way to make the C dll return strings to excel? Thanks DLL.c #ifdef BUILD_DLL #define EXPORT __declspec(dllexport) #else #define EXPORT __declspec(dllimport) #endif EXPORT int __stdcall add2(int num){ return num + 2; ...

Excel #146
can't work this one out can you rotate the data in a spread sheet Colloms to rows ie From: Name Adress Town Post code To Name Address Town Post code Eash item is in a single cell Help!! -- Thanks Bruce Yes, use copy and Paste Special>Transpose "Bruce" wrote: > can't work this one out can you rotate the data in a spread sheet > > Colloms to rows ie > From: > Name > Adress > Town > Post code > > To > Name Address Town Post code > > Eash item is in a single cell > > Help!! > -- > > Thanks Bruce Yes y...