How can I use Excel VBA to select certain ranges depending on input of variables

Question

I am not familiar at all with VBA, but for my lab work, I need to compile data from txt files. I need to copy and paste values in columns together in a specific order. So I thought I could help myself a bit, by using VBA to get rid of unnecessary columns and then select the ranges I need to copy based on my input of variables. For example, if I would need the rows 10 to 60 I would then enter 10 and 60 in my input field and the macro would automatically select the range for me. So far my code looks like this:

Sub Datenanalyse()
'
' Datenanalyse Macro
'
' Keyboard Shortcut: Ctrl+j
    
    Dim c As Long

    For c = Cells(1, Columns.Count).End(xlToLeft).Column To 1 Step -1
        Select Case Cells(1, c).Value
            Case "t", "Time [s]", "Intensity Region 1 ChS1", "Intensity Region 1 Ch2", "Intensity Region 2 ChS1", "Intensity Region 2 Ch2"
                'do nothing
            Case Else
                Columns(c).EntireColumn.Delete
        End Select
    Next c
    
    Dim myValue1 As Variant
    myValue1 = InputBox("First row number") - 1
    
    Dim myValue2 As Variant
    myValue2 = InputBox("Second row number") - 1
    
    Range("C myValue1:F myValue2").Select
    
End Sub

It appears that I cannot use my previously declared variables to use in the selection of the range. How can I change this to accommodate my needs? Thank you for your help. The first part of the code I did not creates a new column in the A1 position and titles it as t and then fills in the cells with linearly increased values starting from 1 up to 1000.

`Range("C" & myValue1 & ":F" & myValue2).Select` Why the -1 ? — Tim Williams, Nov 12 '21 at 23:12
Does this answer your question? [Syntax for Range when using variables](https://stackoverflow.com/questions/37262442/syntax-for-range-when-using-variables) — braX, Nov 12 '21 at 23:51

score 0 · Accepted Answer · answered Nov 12 '21 at 23:14

Just a syntactical issue. Replace:

Range("C myValue1:F myValue2").Select

With

Range("C" & myValue1 & ":F" & myValue2).Select

The above "C" & myValue1 & ":F" & myValue2 constructs a string value for the address range sought. E.g,: if myValue1 was 1 and myValue2 was 10, the result would be C1:F10

How can I use Excel VBA to select certain ranges depending on input of variables

1 Answers1