peispud
asked on
Manipulating a range of cells in Excel from Microsoft Access
Hi
I am using Office 365 - 64 bit
I have a report in Microsoft Access that I wish to export to an excel sheet. This works fine.
Immediately after creating the excel workbook, I wish to modify the some things.
I am using the following code to open the workbook.
I would like to be able to select a range cells(4,1) to the last row / column containing data and manipulate as required
By example, set all the cells in the range = 4. I am having trouble with selecting the range from Microsoft Access VBA.
Thank you
I am using Office 365 - 64 bit
I have a report in Microsoft Access that I wish to export to an excel sheet. This works fine.
Immediately after creating the excel workbook, I wish to modify the some things.
I am using the following code to open the workbook.
Public Function DeleteRowInExcel(ByVal MyFileName As String)
On Error GoTo Err_DeleteRowInExcel
Dim xlApp As Excel.Application
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet
Set xlApp = CreateObject("Excel.Application")
Set wb = xlApp.Workbooks.Open(MyFileName)
Set ws = wb.Sheets(1)
I would like to be able to select a range cells(4,1) to the last row / column containing data and manipulate as required
By example, set all the cells in the range = 4. I am having trouble with selecting the range from Microsoft Access VBA.
Thank you
ASKER
Thank you for your reply but that is not quite what I am looking for.
I know the beginning of the range. The beginning of the range will be cell 4,1
I need the vba code to start from cells(4,1) to the last row and column of data in the worksheet.
Thank you.
I know the beginning of the range. The beginning of the range will be cell 4,1
I need the vba code to start from cells(4,1) to the last row and column of data in the worksheet.
Thank you.
Then use the Columns collection.
Set Range = ws.Columns(3)
The Range variable in this case will hold the content of the whole Column "C"
Set Range = ws.Columns(3)
The Range variable in this case will hold the content of the whole Column "C"
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you for your help.
you're ever so welcome!
Open in new window
Range takes two parameters of Cell type. The first one is the start Cell, and the second one is the end Cell. The example above Clears the content of the Range (A1:C4)