Selecting and editing a range of cells using VBA

[Maypop]

Hi i'm developing a Macro that formats a report, the number of columns and rows used in the document that will be formatted is variable so I can't use fixed ranges. What I need to achieve is to select the first row in column A that doesn't contain data at the bottom of the report and select a range as wide as the columns that have been used. e.g. if there is data 100 rows down and as wide as column E, I need the code to select from a101 to e101 and then:

Merge the cells
set the borders
Wrap text
fill in a fixed amount of text
adjust the height of the row to fit the text in the newly merged cell

I have been able to get to the last row and I have code to select to the last column when I fix the row, I just cant seem to combine them. Any assistance would be appreciated

[TMS]

Post the code you have and ...

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[TMS]

Note: you do NOT have to select the cells in order to manipulate them.

[Richard Buttrey]

Hi,

Why not define and then use a dynamic range name that automatically adjusts. i.e.

myrangename =

Please Login or Register  to view this content.

If there's stuff above A100 in column A then you'll need to adjust the COUNTA(A:A), i.e. COUNTA(A:A)-COUNTA(A1:A99)
and ditto with the number of labels on row 100.

[KOKOSEK]

Did You try use 'Developer/Record macro'?
Do what You exactly describe:

Merge the cells
set the borders
Wrap text
fill in a fixed amount of text
adjust the height of the row to fit the text in the newly merged cell

stop recording ('Developer/Stop recording'), then go to VBA editor (Alt+F11) and move this recorded macro into Your existing macro which getting last row,, edit cells addresses and job is done.

[Maypop]

I managed to solve the issue but thought I would post the code here


Private Sub Create_Disclaimer()

Dim LastCol As Long
Dim LastRow As Long

With ActiveSheet
LastRow = .Cells(.Rows.Count, 1).End(xlUp).Offset(1).Row
LastCol = .Cells(12, .Columns.Count).End(xlToLeft).Column
End With

With ActiveSheet.Range(Cells(LastRow, 1), Cells(LastRow, LastCol))

.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Name = "Calibri"
.Merge
.BorderAround Weight:=xlThin
.RowHeight = 150
.WrapText = True
.Font.Size = 7
.Value = "Text I wanted to insert"
End With

End Sub


Thanks everyone for your input I got there in the end.