Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-10-2024, 09:05 AM
Shelley Lou Shelley Lou is offline VBA Find last cell in Column 2 of Word table Windows 10 VBA Find last cell in Column 2 of Word table Office 2016
Competent Performer
VBA Find last cell in Column 2 of Word table
 
Join Date: Dec 2020
Posts: 171
Shelley Lou is on a distinguished road
Default VBA Find last cell in Column 2 of Word table

I've tried researching on Google on how to find the last cell in a column of a Word table but have been unable to find anything.



When I run my TextToTable code, I would like to change the semi colon in the very last cell of Column 2 to a period/full stop but unsure how to add this to my existing code. Can anyone help at all?

Capture.JPG

Code:
Sub TextToTables()
Dim rRng As Range, rCell As Range 'Convert text to table
Dim oBorder As Border
Dim oTbl As Table
Dim i As Integer
Application.ScreenUpdating = False
Set rRng = ActiveDocument.Range
Set oTbl = rRng.ConvertToTable(Separator:=wdSeparateByTabs, NumColumns:=2, AutoFitBehavior:=wdAutoFitFixed)
With oTbl
   .Style = "Table Grid"
   .ApplyStyleHeadingRows = True
   .ApplyStyleLastRow = False
   .ApplyStyleFirstColumn = True
   .ApplyStyleLastColumn = False
   .Columns.PreferredWidth = InchesToPoints(2.7)
   .Columns(2).PreferredWidth = InchesToPoints(3.63)
For Each oBorder In .Borders
   oBorder.LineStyle = wdLineStyleNone
Next oBorder
Call DPU_ApplyDefinitionStylesToTable
For i = 1 To .Rows.count 'check each row
   Set rCell = .Cell(i, 1).Range 'set a range to the cells in column 1
   rCell.Style = "DefBold" 'apply the style to the range
   Set rCell = .Cell(i, 2).Range  'set a range to the cells in column 2
   rCell.Collapse 1 'collapse the range to its start
   rCell.MoveEndWhile "means,:" 'move the end of the range to include any of these characters
   If rCell.text Like "means*" Then 'if that range starts with 'means'
   rCell.MoveEndWhile Chr(32) 'move the end of the range to include any following spaces
   rCell.text = "" 'and empty the range
End If
   Next i
   End With
Application.ScreenUpdating = True
Set rRng = Nothing
Set oTbl = Nothing
Set rCell = Nothing
Set oBorder = Nothing
End Sub
Reply With Quote
  #2  
Old 04-10-2024, 10:11 AM
Italophile Italophile is offline VBA Find last cell in Column 2 of Word table Windows 11 VBA Find last cell in Column 2 of Word table Office 2021
Expert
 
Join Date: Mar 2022
Posts: 341
Italophile is just really niceItalophile is just really niceItalophile is just really niceItalophile is just really nice
Default

In the code block after
Code:
 Set rCell = .Cell(i, 2).Range
add a test for
Code:
If i = .Rows.Count Then
add add in your code to replace the semi-colon
Reply With Quote
  #3  
Old 04-11-2024, 12:38 AM
Shelley Lou Shelley Lou is offline VBA Find last cell in Column 2 of Word table Windows 10 VBA Find last cell in Column 2 of Word table Office 2016
Competent Performer
VBA Find last cell in Column 2 of Word table
 
Join Date: Dec 2020
Posts: 171
Shelley Lou is on a distinguished road
Default VBA Find last cell in Column 2 of Word table

Hi, apologises but don't think I understand - does this line of code find only the very last cell in Column 2 - I only need to change the very last semi-colon not all of them?
Reply With Quote
  #4  
Old 04-11-2024, 01:49 AM
Guessed's Avatar
Guessed Guessed is offline VBA Find last cell in Column 2 of Word table Windows 10 VBA Find last cell in Column 2 of Word table Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,987
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
Default

In your example the last cell in column 2 is also the last cell in the table. Looking at your previous question, I'm going to assume that is always the case. I'm also going to assume there are no trailing spaces on that last cell so this simple code would work.
Code:
Sub CleanseTheColon()
  Dim aTbl As Table, aRng As Range
  For Each aTbl In ActiveDocument.Tables
    Set aRng = aTbl.Range
    aRng.End = aRng.End - 2
    If aRng.Characters.Last = ";" Then aRng.Characters.Last = "."
  Next aTbl
End Sub
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
  #5  
Old 04-11-2024, 03:45 AM
Shelley Lou Shelley Lou is offline VBA Find last cell in Column 2 of Word table Windows 10 VBA Find last cell in Column 2 of Word table Office 2016
Competent Performer
VBA Find last cell in Column 2 of Word table
 
Join Date: Dec 2020
Posts: 171
Shelley Lou is on a distinguished road
Default VBA Find last cell in Column 2 of Word table

Absolutely perfect Andrew, thank you so much
Reply With Quote
  #6  
Old 04-11-2024, 04:04 AM
Italophile Italophile is offline VBA Find last cell in Column 2 of Word table Windows 11 VBA Find last cell in Column 2 of Word table Office 2021
Expert
 
Join Date: Mar 2022
Posts: 341
Italophile is just really niceItalophile is just really niceItalophile is just really niceItalophile is just really nice
Default

Quote:
Originally Posted by Shelley Lou View Post
Hi, apologises but don't think I understand - does this line of code find only the very last cell in Column 2 - I only need to change the very last semi-colon not all of them?
Your code loops through the rows in the table. Unless there are vertically merged cells the last cell in a column will be in the last row of the table.

As you are using i as the row number the statement
Code:
 If i = .Rows.Count Then
indicates that you are in the last row and that rCell is, therefore, the last cell in column 2.
Reply With Quote
Reply



Similar Threads
Thread Thread Starter Forum Replies Last Post
VBA Find last cell in Column 2 of Word table What formula can I use to find the lowest cell in a column that has a value in it? EddyWD Excel 3 04-20-2016 09:29 PM
VBA Word - Find Specific Table - Prepend & Append Data to Each Cell jc491 Word VBA 3 12-02-2015 09:48 PM
VBA Find last cell in Column 2 of Word table VBA Table – Search All Tables - Find & Replace Text in Table Cell With Specific Background Color jc491 Word VBA 8 09-30-2015 06:10 AM
Find text within cell and return column and row title next to the name on a new sheet. tanyabowring@live.co.uk Excel Programming 2 03-26-2015 01:48 AM
How do I reference a merged cell in a multi column & row table in MS Word ('03')? jihanemo Word Tables 0 03-18-2009 08:33 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 09:33 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft