获取最后一栏& Excel工作表中的数据行 [英] Get Last Column & Row of Data in Excel Worksheet

问题描述

大家好 - 这段代码有点问题。它可以在我拥有的其中一个数据库中工作但不是我正在使用的数据库...我已经比较了参考文献，它们都是相同的：


当我试图从我的Excel文件中的每个工作表中获取最后一列数据时，它将最后一列作为c给我。当它应该是AH时或AI，取决于它所在的工作表。在一个工作表上，它'给了我']并且没有]工作表中的任何位置。最后一个数字是正确的 - 只是不正确的列。


这里是我正在使用的代码（我从这里获得的代码 - 非常感谢贡献者）

展开 | 选择 | Wrap | < span class =codeLinkonclick =LineNumbers（this）;>行号

解决方案

我的建议是删除范围，因为它您似乎正在尝试从电子表格导入所有数据。如果我弄错了，请告诉我。我查看了您提供的代码，并希望尝试回答有关+64的问题。我会说，看起来有人尝试使用Chr函数返回字母表（字符集）中的字母，以便在TransferSpreadsheet函数的范围内使用。如果要导入的列数超过26，则此代码会出现更多问题。

我建议删除范围的另一个原因是Selection.SpecialCells（xlLastCell）.Column不会总是返回最后一列。我已经看到了这样的例子，我会在导入过程中试图远离使用范围，因为出现了类似这样的问题。

希望这个帮助

确实代码正在尝试生成一个字符（Chr（...）），因为Denburt推测。

Selection.SpecialCells（xlLastCell）将给出工作表中当前使用的最后一个单元格（或者更准确地说，是使用的最后一列和最后一行的交集）。

如果它返回的列，这可能看起来不是你想要的那个：

• 隐藏
• 已删除，但工作簿尚未保存，因为
• 内容已清除但未删除

请记住，这不是包含可见数据的最后一列。它相当于在Excel中键入Ctrl-End后返回选中的单元格。

确实代码正在尝试生成一个字符（ Chr（...））作为Denburt推测。

Selection.SpecialCells（xlLastCell）将给出工作表中当前使用的最后一个单元格（或者更准确地说，是最后一列使用的和使用的最后一行）。

如果它返回的列，这可能看起来不是你想要的那个：

• 隐藏
• 已被删除但是工作簿
• 内容已被清除但未删除

请记住，这不是包含可见数据的最后一列。它相当于在Excel中键入Ctrl-End后返回选中的单元格。

感谢您的澄清Neo听起来好像我现在可能有一个Excel goto家伙。 ：）

Hi all - having a bit of problem with this code. It works in one of the databases that I have but not the one I''m currently working with...I''ve compared references and they''re both the same:

When trying to get the last column of data from each worksheet in my Excel file, it''s giving me the last column as "c" when it should be "AH" or "AI,'' depending on which worksheet it''s on. On one worksheet, it''s giving me "]" and there is no "]" anywhere in the worksheet. The lastdatarow number is correct - just the column that isn''t right.

Here''s the code I''m using (which I got from here-many thanks to the contributor)

Expand|Select|Wrap|Line Numbers

解决方案

My suggestion would be to remove the range since it it appears that you are trying to import all the data from the spreadsheet. If I am mistaken then let me know. I looked over the code you supplied and would like to try to answer your question regarding the +64. I would say that it looks like someone made an attempt at returning a letter from the alphabet (Character Set) using the Chr function to be used in the range for the TransferSpreadsheet function. If the numbers of your columns to be imported exceeds 26 then you would have more issues with this code.
Another reason I suggest removing the range is due to the fact that Selection.SpecialCells(xlLastCell).Column will not always return the last column. I have seen instances where it would be benificial fore something like this I myself try to stay away from using a range during import, due to the number of issues that pop up for something like this.
Hope this helps

Indeed the code is trying to produce a character (Chr(...)) as Denburt surmises.
Selection.SpecialCells(xlLastCell) will give the last cell currently used in the worksheet (or, more precisely, the intersection of the last column used and the last row used).
This may not appear to be the one you want if the column it returns :

• Is hidden
• Has been deleted but the workbook has not been saved since
• The contents have been cleared but not deleted

Remember, this is not the last column with visible data in it. It is equivalent to returning the cell selected after you type Ctrl-End within Excel.

Indeed the code is trying to produce a character (Chr(...)) as Denburt surmises.
Selection.SpecialCells(xlLastCell) will give the last cell currently used in the worksheet (or, more precisely, the intersection of the last column used and the last row used).
This may not appear to be the one you want if the column it returns :

• Is hidden
• Has been deleted but the workbook has not been saved since
• The contents have been cleared but not deleted

Remember, this is not the last column with visible data in it. It is equivalent to returning the cell selected after you type Ctrl-End within Excel.

Thanks for the clarifications Neo sounds like I may have an Excel goto guy now. :)

这篇关于获取最后一栏&amp; Excel工作表中的数据行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！