If you need VBA, you could do something quick like this: This will print the number of the last row with data in it. Obviously don’t need MsgBox in there if you’re using it for some other purpose, but lastRow will become that value nonetheless.
You need an: in your code.
First of all, try to run Visual Studio with the /ResetUserData command line argument. Read more about that in the Error “Unable to cast COM object…” when exporting to Microsoft Excel from Team Explorer 2008 article. Obviously you are trying to connect to a wrong Excel version. Looks like you have some extra windows registry … Read more
This question was answered well at Can you encode CR/LF in into CSV files?. Consider also reverse engineering multiple lines in Excel. To embed a newline in an Excel cell, press Alt+Enter. Then save the file as a .csv. You’ll see that the double-quotes start on one line and each new line in the file … Read more
Place single quotes around the Extended Properties: Try it, it really works.
I am using Excel for Mac 2016 on macOS Sierra software. Although I have been successfully copying and pasting CSV files into excel for some time now, recently, they have begun to behave in an odd way. When I paste the data, the content of each row seems to split over many columns. Where as … Read more
To illustrate my issue, use the following simple example. I want to be able to replace any text that comes between “replace” and “that” to “it is done”. For example, the two following strings: to this string using the SUBSTITUTE function in excel using wildcard characters. This is the formula that I’m using: The formula … Read more
You can’t nest a function inside a procedure. You need to move it above:
Use the below function (changing [E:E] to the appropriate range for your needs) to circumvent this issue (or change to any other format such as “mm/dd/yyyy”): P.S. In my experience, this VBA solution works SIGNIFICANTLY faster on large data sets and is less likely to crash Excel than using the ‘warning box’ method.
Use the standard “Open” command. Then go to “Text Import Wizard” and choose that the original data type was delimited. You may need to go to a different step of the Wizard to specify that the delimiter is a space, but the Wizard does include the option of specifying what character the delimiter is.