# Measuring distances with vba

#### dpaton05

##### Well-known Member
Can you use vba to measure distances in excel, such as between any given row at the bottom of the current page?

### Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

#### mikerickson

##### MrExcel MVP
What units do you want the result to be in?
By distance between a given row, do you mean distances between two given ranges?
Are these physical distances (like measuring on the computer screen) or distances in a virtual metric (so many rows up, so many columns over)?

To answer your question, yes. Each cell has a .Left and a .Top property that can be used for physical measurements.

Last edited:

#### dpaton05

##### Well-known Member
Thanks for the reply Mike. I have a button to insert an image automatically and one to insert a custom image, both images are to appear below the contents of a textbox. The textbox is the lowest thing on the sheet. I basically want to see if the inserted image would fit between a text box and the bottom of the current sheet/page.

#### dpaton05

##### Well-known Member
I thought I could find the last row the textbox extended down to and measure the distance between that row and the end of the page. I think I would need it in the same units that the measurements for the image were in. I don't know what those measurements are,

#### mikerickson

##### MrExcel MVP
ActiveWindow.VisibleRange is the range object of the cells currently visible

So, this should give you two numbers to locate your new image.
Code:
``````Dim tbxBottom as Single, lastRowTop as Single

With ActiveSheet.Shapes("TextBox 1")
tbxBottom = .Top + .Height
End With

With ActiveWindow.VisibleRange
lastRowTop = .Rows(.Rows.Count).Top
End With``````

• dpaton05

#### dpaton05

##### Well-known Member
With ActiveWindow.VisibleRange
lastRowTop = .Rows(.Rows.Count).Top
End With[/CODE]
So this finds the top of the last row of the current page?

#### mikerickson

##### MrExcel MVP
Yes it does. Note that most of the last row might be off the screen.

#### dpaton05

##### Well-known Member
I am going round and round in circles with my problem solving on this Mike. Could you help me please with the code for the custom image?

I need to insert it below a textbox but if the image gets split between 2 pages, the image needs to be pushed to the second page.
This is the code I have so far:
Code:
``````Sub cmdCustomSig()
Dim fNameAndPath As Variant
Dim img As Picture, shp As Shape
Set shp = ThisWorkbook.Worksheets("quote_sheet").Shapes("textbox4")

fNameAndPath = Application.GetOpenFilename(Title:="Select Picture To Be Imported")
If fNameAndPath = False Then Exit Sub
Set img = Worksheets("quote_sheet").Pictures.Insert(fNameAndPath)
With img
.Top = shp.Top + shp.Height + "50"
.Left = "0"
End With
End Sub``````

#### jolivanes

##### Well-known Member
Looks like you need to work with the difference of your first horizontal page break and the bottom of your textbox4.

Code:
``````Sub Find_First_Hor_PageBreak()
Dim iRow
iRow = Sheets("Sheet1").HPageBreaks(1).Location.Row
MsgBox iRow
End Sub``````

#### dpaton05

##### Well-known Member
But how would I compare that to the image height to see if it fits?