Find last row & last column of changing range

RJS

New Member
Joined
Sep 13, 2002
Messages
2
The following code copies data from Excel & pastes it as unformatted text into a new Word doc.

The range to be copied will always start at A15, but can consist of any number of rows & any number of columns up to & including H, e.g. A15:C300, A15:E20, A15:H2000, etc.

My problem is to find the last row & column with data.

The code below doesn't work properly -- the range selected includes cells with no visible data (false positives?), which create problems when copied into the Word doc.

Any help greatly appreciated! I'm a mere cutter & paster with little knowledge of VBA, so please feel free to underestimate my level of expertise.

______________________
Sub report()
Dim Rng As Range
Dim LastRow As Long
Range("A65536").Select
ActiveCell.End(xlUp).Select
LastRow = ActiveCell.Row
Set Rng = Range("A15" & ":" & "H" & LastRow)
Application.Goto Reference:=Rng
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy
'open Word & paste
Dim appWD As Word.Application
Set appWD = CreateObject("Word.Application")
appWD.Visible = True
appWD.Documents.Add
appWD.Selection.PasteAndFormat (wdPasteDefault)
End Sub
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Richie(UK)

MrExcel MVP
Joined
May 17, 2002
Messages
3,329
Hi RJS,

Welcome to the board. :)

I'm not too hot on the formatting of the pasted information in Word but I think this routine should do most of what you wanted.

<pre>
Sub report()
Dim Rng As Range
Dim LastRow As Long
Dim LastColumn As Integer
Dim appWD As Object 'keep all Dim items at the start

'/// Find the last row ///
If WorksheetFunction.CountA(Cells) > 0 Then
'Search for any entry, by searching backwards by Rows.
LastRow = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
End If

'/// Find the last column ///
If WorksheetFunction.CountA(Cells) > 0 Then
'Search for any entry, by searching backwards by Columns.
LastColumn = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column
If LastColumn > 8 Then LastColumn = 8 'No further than "H"
End If

Set Rng = Range(Cells(15, 1), Cells(LastRow, LastColumn))
Rng.Copy

'open Word & paste
Set appWD = CreateObject("Word.Application")
appWD.Visible = True
appWD.Documents.Add
appWD.Selection.Paste

Application.CutCopyMode = False 'release memory from Clipboard
Set appWD = Nothing 'release memory from object variable

End Sub

</pre>

HTH
 

RJS

New Member
Joined
Sep 13, 2002
Messages
2
Thanks for that, Richie! It works fine, except that I still have rogue blank rows & columns in the Word document.

I'm wondering whether some ranges which are used in previous operations in the source spreadsheet are creating the problem (a separate macro to select the data to be copied precedes the one I gave above).

I may see if I can build back some of your macro's logic into the previous macro to prevent this (i.e. to avoid using pre-set ranges).

Thanks again!
 

Forum statistics

Threads
1,148,033
Messages
5,744,430
Members
423,872
Latest member
ConorN

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top