I need to reference a dynamic cell range in vba code

[senthia87]

How do i reference a cell range in vba when the range is dynamic?

Basically I have a table underneath rows of data, at anytime data can be inserted into those rows. So everytime data is inserted my table shifts down a row and the cell reference in my vba code no longer match up. Is there a way to fix this?

 

[James Snyder]

Is the row being shifted down in the macro, or by formula?

 

[senthia87]

There is a row inserted via macro when a button is clicked that is linked to the code. I can't put the table above the rows of data because there is stuff there that cannot be moved without messing with another system it has to go into.

 

[JackDanIce]

Depends on the position of the cell and how you identify it. Say you want the last used cell in a column but don't know what row it's on, you could use:

Range("A" & rows.count).end(xlup).select

This will select the last used cell in column A, regardless of how many rows are above it or if rows are inserted or deleted

 

[senthia87]

[TABLE="width: 1503"]
<TBODY>[TR]
[TD]


[/TD]
[TD]Not Current</SPAN>
[/TD]
[TD]Current</SPAN>
[/TD]
[TD][/TD]
[TD]Not Current </SPAN>
[/TD]
[TD]Current</SPAN>
[/TD]
[TD][/TD]
[TD]Not Current</SPAN>
[/TD]
[TD]Current</SPAN>
[/TD]
[TD][/TD]
[TD]Not Current </SPAN>
[/TD]
[TD]Current</SPAN>
[/TD]
[TD][/TD]
[TD]Not Current </SPAN>
[/TD]
[TD]Current</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]1</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]26</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]50</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]75</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]99</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]27</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]51</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]76</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]100</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]28</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]52</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]77</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]101</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]29</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]53</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]78</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]102</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]30</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]54</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]79</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]103</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]31</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]55</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]80</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]104</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]32</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]56</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]81</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]105</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]33</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]57</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]82</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]106</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]34</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]58</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]83</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]107</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10</SPAN>
[/TD]
[TD="align: right"]9</SPAN>
[/TD]
[TD="align: right"]9</SPAN>
[/TD]
[TD="align: right"]35</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]59</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]84</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]108</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]11</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]36</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]60</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]85</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]12</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]37</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]61</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]86</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]13</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]38</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]62</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]87</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]14</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]39</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]63</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]88</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]15</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]40</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]64</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]89</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]16</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]41</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]65</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]90</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]17</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]42</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]66</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]91</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]18</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]43</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]67</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]92</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]19</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]44</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]68</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]93</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]20</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]45</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]69</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]94</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]21</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]46</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]70</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]95</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]22</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]47</SPAN>
[/TD]
[TD="align: right"]8</SPAN>
[/TD]
[TD="align: right"]8</SPAN>
[/TD]
[TD="align: right"]71</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]96</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]23</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]48</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]72</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]97</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]24</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]49</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]73</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]98</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]25</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]74</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]

The range of the table is from A36:O61. I want to reference the noncurrent cell for each number in my code.

 

[James Snyder]

Using his code above:

Dim rowA As Long
Dim rowB As Long
Dim currentA As String
Dim currentB As String
Dim refA As Range
Dim refB As Range
currentA = "B"
currentB = "D"
rowA = Range(currentA & rows.count).end(xlup).row
rowB = Range(currentB & rows.count).end(xlup).row
refA = .Range(currentA & rowA)
refB = .Range(currentB & rowB)

 

[senthia87]

I add the first code and then the one that was just posted?


This is the code i have right now. The bolded cells are the ones that need to be referenced even though the rows might shift. I wish i could attach a sample spreadsheet.

Private Sub Workbook_BeforeClose(cancel As Boolean)
'Run macro to last row of data
finalrow = Cells(Rows.Count, 1).End(xlUp).Row
'Assign variables to individual cases
For i = 2 To finalrow
Select Case Cells(i, 40).Value
Case "ABC1"
Cells(i, 41) = 1
Case "DEF1"
Cells(i, 45) = 2
Case Else
End Select
Next i
'ABC
finalrow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 33 To finalrow
If Cells(i, 43).Value > [B47] Then
Cells(i, 44).Value = "YES"
ElseIf Cells(i, 44).Value <= [B47] Then
Cells(i, 44).Value = " "
End If
Next
'DEF
finalrow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 33 To finalrow
If Cells(i, 47).Value > [E59] Then
Cells(i, 48).Value = "YES"
ElseIf Cells(i, 47) <= [E59] Then
Cells(i, 48).Value = " "
End If
Next
End Sub

I appreciate your help.