bookmark_borderExcel VBA: Last Used Column

Similar to my previous post on finding the last used row in a sheet, this is a function that can be used to find the last used column. These two functions work nicely together to limit a loop or define a range when the source data may vary in size.

The function: getLastCol():

  • If called with no parameters, the last used column in the active worksheet will be returned.
  • The optional sheetName parameter lets you reference a sheet other than the one that is active.
  • The optional rowNum parameter returns the last used column in a particular row.
  • The optional colLimit parameter limits the total number of iterations in the loop when searching for the last used column, this can be used to speed up execution when you have an idea of what the sheet may contain already.
  • If there is an error with the parameters it will return zero

Examples:

getLastCol() = 3
getLastCol("Fruit") = 3
getLastCol(,3) = 2 'last used column on row 3 only
getLastCol(Vegetables) = 0 'this sheet name does not exist

The Code:

Function getLastCol(Optional sheetName As String, Optional rowNum As Long, Optional colLimit As Long) As Long
'by Elliot 7/22/20 www.elliotmade.com
'this function will return the last used column on a sheet in a single row
'if no sheet name is specified it will use the active sheet
'if no row is specified it will find the last column in any row up to an optional limit (for faster performance)
'two assumptions are made: the file type is .xlsx or similar that supports 16k columns
'and the sheet is in the active workbook
'a zero returned means that there was a failure

Dim i As Long
Dim j As Long
Dim curLastCol As Long

'check for valid inputs first, return zero if there is a problem
If rowNum < 0 Or rowNum > 1048575 Then GoTo abort

If sheetName <> "" Then
    For i = 1 To Worksheets.Count
        If Worksheets(i).Name = sheetName Then GoTo sheetOK
    Next i
    GoTo abort 'specified sheet name was not found
sheetOK:
End If

If colLimit = 0 Then
    colLimit = 16384
ElseIf colLimit < 0 Or colLimit > 16384 Then
    GoTo abort
End If

If sheetName = "" Then sheetName = ActiveSheet.Name

'if no problem, find the last column
If rowNum = 0 Then
    getLastCol = Worksheets(sheetName).Cells(1, 16384).End(xlToLeft).Column
    For j = 1 To colLimit
        If Worksheets(sheetName).Cells(1048575, j).End(xlUp).Row > 1 Then curLastCol = j
        If curLastCol > getLastCol Then getLastCol = curLastCol
    Next j
Else
    getLastCol = Worksheets(sheetName).Cells(rowNum, 16384).End(xlToLeft).Column
End If

abort:

End Function

You may notice that the method using xlToLeft is used to find the last column in a specific row, but I did not use it when searching for the last column in the entire sheet; to do this in a loop would require iterating through every single row (over 1M!) and would take a significant amount of time. I chose to do two things to shortcut this process: first, check the first row because it often has headings for the rest of the document, and second, find the last used row in every column instead. This limits the possible trips through the loop to 16k at most (the number of columns in a sheet). I have some other ideas to improve on this, but I’ll leave it alone for now until it becomes a bottleneck.

Next I’ll post an example that puts this function to use, stay tuned.

bookmark_borderExcel VBA: Last Used Row

If you’re getting into Excel macros and have fooled around with the macro recorder you may notice that the resulting code is very rigid: it only works on the exact range of cells you had selected for example (among other drawbacks). In many cases you want your code to adapt to the size of the data in a sheet, and for this you need to determine the boundaries of the range.

.UsedRange Property

If you’ve done a search you may have come across this approach already. .UsedRange.Rows.Count will usually give the right result if you’re looking for the last used row on a particular sheet, but not always. For whatever reason, this property is not always current – sometimes it is possible to clear the contents of a row without causing this to update, giving an incorrect result. Saving the document (and probably some other actions) will cause this to be updated, but I don’t consider it to be reliable enough to base further logic on.

.End(xlUp).row

This approach is the equivalent of putting your cursor in a cell and pushing CTRL+UP. If you do this on the very last row of a sheet, the cell that your cursor lands on will be the last one in that column. This works reliably, but only gives a result for one column, not the entire sheet. If you put this together with a loop and repeat it for all columns (or a reasonable number) you can reliably retrieve the last used row on a sheet.

The function: getLastRow()

Putting this all together, an easy to use function can be made that returns the last row number on a sheet or in a specific column.

  • If called with no parameters it will return the last used row in the first 100 columns of the active sheet.
  • The optional sheetName parameter is useful to reference a sheet other than the active one, or if you can’t guarantee which sheet is active when the function is called.
  • The second optional parameter, colNum, can be used if you want to know the last used row in a specific column.

Examples

Here are some examples of how this works with the sample sheet below:

getLastRow() = 8 'from the active sheet
getLastRow("Fruit") = 8 'even when a different sheet is active
getLastRow(,3) = 6 'from the active sheet
getLastRow("Fruit", 3) = 6 'even when a different sheet is active
getLastRow("Fruit", 4) = 1 'this column is empty
getLastROW("Vegetables") = 0 'this sheet name is invalid

And finally, the function itself:

Function getLastRow(Optional sheetName As String, Optional colNum As Long) As Long
'by Elliot 7/22/20 www.elliotmade.com
'this function will return the last used row on a sheet or a single row
'if no sheet name is specified it will use the active sheet
'if no column is specified it will find the last row in any of the first 100 columns
'two assumptions are made: the file type is .xlsx or similar that supports ~1M rows
'and the sheet is in the active workbook

'a zero returned means that there was a failure

Dim i As Long
Dim j As Long
Dim curLastRow As Long

'check for valid inputs first, return zero if there is a problem
If colNum < 0 Or colNum > 16384 Then GoTo abort

If sheetName <> "" Then
    For j = 1 To Worksheets.Count
        If Worksheets(j).Name = sheetName Then GoTo sheetOK
    Next j
    GoTo abort 'specified sheet name was not found
sheetOK:
End If

If sheetName = "" Then sheetName = ActiveSheet.Name

'if no problem, find the last row
If colNum = 0 Then
For i = 1 To 100
    curLastRow = Worksheets(sheetName).Cells(1048575, i).End(xlUp).Row
    If curLastRow > getLastRow Then getLastRow = curLastRow
Next i

Else
    curLastRow = Worksheets(sheetName).Cells(1048575, colNum).End(xlUp).Row
End If

abort:

getLastRow = curLastRow

End Function

If you found this useful, or if you have a problem this might help you solve, let me know!

bookmark_borderTreadmill Controller Reverse Engineering

I scrapped a treadmill and salvaged the motor for a belt grinder project. My goal is to chuck the original control panel and just use the motor and it’s controller… but unlike the previous treadmill I did this to, interfacing with this one was not as simple as feeding it a PWM signal. The motor is a Johnson 90v DC motor, model JM01-013, and the control board is labeled MLH0910PC. I’m hoping the following information helps at least one other person – I spent hours searching and wasn’t able to come up with anything useful.

The control panel (I’ll refer to this as the panel going forward) has all of the displays and connects to the rest of the interface buttons and the safety key switch. The motor controller contains the AC connection, runs the motor and incline, as well as supplies power for the panel.

There is a single cable connecting the controller and panel with 8 wires: two are the safety key switch and pass straight through to the controller, two for power, two for ground, and the last two are for serial data. On both ends of the connector there is a MAX3085 RS-485 transceiver chip. Initially I used a MAX485 on a breakout board to listen in, but I was struggling to extract useful data with a logic analyzer and PulseView. It was clear that there was a message and response from one unit to the other, but for whatever reason I wasn’t able to reliably pick up the start and end bits for the response. I had much better luck connecting the logic analyzer directly to pins 1 and 4 of the MAX3085 (on either board works)… this let me see the RX and TX data on separate channels, then it could be decoded easily.

Logic analyzer connected to pins 1 and 4

From what I can tell the panel is the master, it always initiates communication (either a command or a query), and the controller only responds. Messages are sent roughly every 70-100 ms at 9600 baud and consist of 5-7 bytes. The first two bytes look like an identifier: the panel prefix is 0-255, and the controller is 0-127. I looked at all of this in decimal because why not, also it made it easy to work with in Excel. I was able to isolate the packet from the panel that seemed to control the speed, then I recorded the values for every speed available from .5 to 12 MPH in tenth of a MPH increments.

I used an arduino to replay all of the messages through the MAX485 from power-up to running and was pleased to see the motor start to run (I kept the safety switch shorted). Working backwards, I started eliminating the different messages until all I had left was the speed command and the motor continued to run – showing me that I can ignore pretty much everything else – great! Did some more experimentation and here is what I found:

  • Only speed commands are needed
  • All acknowledgements from the controller can be ignored
  • Any new speed lower than the current one will be accepted by the controller and it will coast down to that setting
  • A new speed higher than the current one will be accepted if it is not too much higher. For example, the motor will accelerate from 5 to 5.1 mph
  • A new speed too much higher than the current one will be rejected and the motor will coast to a stop. For example, commanding it to accelerate from 5 to 12 mph.
  • Speed changes are very gradual (probably to keep you from falling on your face and/or butt)
  • It won’t exceed 4000 RPM (12 mph), or perhaps I picked bogus values when I tried

This means that interfacing with this is going to be pretty easy. My approach was just to create a lookup table for the known speeds that I mapped out. When increasing the speed I ramp through each of the steps on the way to the final target speed, this seems to keep the controller happy and it doesn’t panic and coast to a stop. There is no need to ramp down to a slower speed, only to a higher one.

Example packet

The 7-byte packet that controls speed is pictured above. I mapped all of the speeds from zero to twelve MPH, see the table below. RPM was measured with no load, but I expect it to be similar under load – the motor has a toothed wheel (maybe optical or probably hall-effect) and I’m guessing it uses closed-loop speed control.

Mission accomplished! I’ve actually had this motor sitting around for over a year but never made any headway on figuring out how to control it, now I can move on and make something useful with it.

MPHByte 1Byte 2Byte 3Byte 4Byte 5Byte 6Byte 7Measured RPM
0.002552412002210
0.502552412016116 
0.6025524120195201 
0.7025524120230186 
0.80255241218144 
0.902552412142116 
1.002552412176105332
1.1025524121111188 
1.202552412114533 
1.3025524121179197 
1.4025524121213216 
1.502552412124818 
1.602552412226156 
1.702552412260188 
1.802552412294101 
1.902552412212979 
2.0025524122163171670
2.1025524122197182 
2.202552412223182 
2.30255241231043 
2.40255241234411 
2.502552412378210 
2.60255241231128 
2.7025524123147154 
2.8025524123181186 
2.902552412321599 
3.00255241232492501016
3.10255241242896 
3.202552412462132 
3.302552412496229 
3.402552412413070 
3.502552412416587 
3.6025524124199142 
3.702552412423323 
3.80255241251164 
3.90255241254651 
4.0025524125802121358
4.102552412511448 
4.202552412514887 
4.3025524125183130 
4.402552412521738 
4.5025524125251194 
4.602552412629136 
4.702552412664186 
4.80255241269894 
4.902552412613257 
5.00255241261662211700
5.1025524126200121 
5.2025524126235172 
5.30255241271363 
5.402552412747219 
5.50255241278160 
5.602552412711679 
5.7025524127150236 
5.8025524127184117 
5.9025524127218172 
6.00255241272531892042
6.102552412831135 
6.202552412865230 
6.3025524128992 
6.402552412813454 
6.5025524128164175 
6.6025524128202118 
6.702552412823686 
6.802552412915112 
6.902552412949234 
7.002552412983512385
7.102552412911719 
7.2025524129152158 
7.3025524129186122 
7.4025524129220103 
7.5025524129254131 
7.6025524121033132 
7.702552412106793 
7.80255241210101125 
7.90255241210135222 
8.00255241210170202725
8.102552412102049 
8.20255241210238237 
8.3025524121116132 
8.402552412115181 
8.502552412118576 
8.60255241211119168 
8.70255241211153118 
8.802552412111885 
8.90255241211222220 
9.0025524121201053070
9.1025524121234141 
9.20255241212101161 
9.3025524121210369 
9.40255241212137139 
9.50255241212171127 
9.6025524121220649 
9.70255241212240235 
9.8025524121318188 
9.9025524121352156 
10.00255241213871163414
10.10255241213121237 
10.2025524121315578 
10.30255241213173110 
10.4025524121322492 
10.502552412142210 
10.6025524121436242 
10.702552412147043 
10.80255241214105131 
10.9025524121413932 
11.0025524121417303752
11.10255241214207217 
11.2025524121424280 
11.3025524121520195 
11.402552412155439 
11.5025524121588131 
11.6025524121512386 
11.7025524121515749 
11.80255241215191213 
11.90255241215225180 
12.0025524121641193996

Edit 10/25/21: Here are the codes blinked out by the control board:

And here is a simple arduino sketch. You can probably ignore the 7-segment and rotary encoder parts, but it is successfully controlling the motor drive through a MAX485 chip on a breakout board.


/*
 * Encoder on A2, A3 
 * Button on A1
 * MAX 485: 5v to DE, D8 to DI.  A and B to A and B of the treadmill controller
 * TM1637 display clock on 2, data on 3
 * Short out the middle "safety key" pins 
 * Take power from one of the treadmill controller pins (which one?)
 * Treadmill header has 8 pins (left to right, latch at the bottom):
 * 1 - 12v
 * 2 - 12v
 * 3 - A or B?
 * 4 - Safety switch
 * 5 - Safety switch
 * 6 - A or B?
 * 7 - Ground
 * 8 - Ground
*/




#include <AltSoftSerial.h>
//https://www.pjrc.com/teensy/td_libs_AltSoftSerial.html

#include <SimpleTimer.h>
//https://github.com/jfturcot/SimpleTimer

#include <RotaryEncoder.h>
//http://www.mathertel.de/Arduino/RotaryEncoderLibrary.aspx
//https://github.com/mathertel/RotaryEncoder

#include "OneButton.h"
//https://github.com/mathertel/OneButton

#include <TM1637Display.h>
//https://github.com/avishorp/TM1637

#include <EEPROMex.h>
//https://github.com/thijse/Arduino-EEPROMEx

const int encoderButton = A1;
const int encoderA = A2;
const int encoderB = A3;
const int dispClock = 2; //D2
const int dispData = 3; //D3


AltSoftSerial ss;
SimpleTimer timer;
OneButton encoderButt(encoderButton, true);
RotaryEncoder encoder(A2, A3);
TM1637Display display1(dispClock, dispData);

bool halt = true;
int setSpeed = 0;
int curSpeed = 0; //ramp this up until it reaches the set speed
int savedSpeed = 0;
const int speedCount = 123;

const byte b1[] = {0,0,0,0,1,1,1,1,1,1,1,1,2,2,2,2,2,2,2,3,3,3,3,3,3,3,3,4,4,4,4,4,4,4,5,5,5,5,5,5,5,5,6,6,6,6,6,6,6,7,7,7,7,7,7,7,7,8,8,8,8,8,8,8,9,9,9,9,9,9,9,9,10,10,10,10,10,10,10,11,11,11,11,11,11,11,12,12,12,12,12,12,12,12,13,13,13,13,13,13,13,14,14,14,14,14,14,14,14,15,15,15,15,15,15,15,16,16,16,16,16,16,16,16};
const byte b2[] = {0,161,195,230,8,42,76,111,145,179,213,248,26,60,94,129,163,197,231,10,44,78,112,147,181,215,249,28,62,96,130,165,199,233,11,46,80,114,148,183,217,251,29,64,98,132,166,200,235,13,47,81,116,150,184,218,253,31,65,99,134,164,202,236,15,49,83,117,152,186,220,254,33,67,101,135,170,204,238,16,51,85,119,153,188,222,0,34,101,103,137,171,206,240,18,52,87,121,155,173,224,2,36,70,105,139,173,207,242,20,54,88,123,157,191,225,4,20,54,88,123,157,191,225};
const byte b3[] = {221,16,201,186,144,116,105,188,33,197,216,18,156,188,101,79,171,182,82,43,11,210,8,154,186,99,250,96,132,229,70,87,142,23,64,51,212,48,87,130,38,194,136,186,94,57,221,121,172,63,219,60,79,236,117,172,189,135,230,2,54,175,118,86,112,234,51,19,158,122,103,131,132,93,125,222,20,9,237,132,81,76,168,118,5,220,105,141,161,69,139,127,49,235,188,156,116,237,78,110,92,210,242,43,131,32,0,217,80,195,39,131,86,49,213,180,119,195,39,131,86,49,213,180};
const int sfm[] = {0,262,314,366,418,471,523,575,628,680,732,785,837,889,941,994,1046,1098,1151,1203,1255,1308,1360,1412,1464,1517,1569,1621,1674,1726,1778,1831,1883,1935,1987,2040,2092,2144,2197,2249,2301,2355,2406,2458,2510,2563,2615,2667,2721,2772,2824,2878,2929,2981,3033,3087,3138,3190,3244,3295,3347,3401,3453,3504,3556,3610,3661,3713,3767,3818,3870,3924,3976,4027,4079,4133,4184,4236,4290,4342,4393,4447,4499,4550,4602,4656,4708,4759,4813,4865,4916,4970,5022,5074,5125,5179,5231,5282,5336,5388,5440,5493,5545,5597,5648,5702,5754,5805,5859,5911,5963,6016,6068,6120,6171,6225,6277,6329,6382,6434,6486,6539,6591,6643};

//eeprom memory locations
const int memAddress = 20;
const int memBase = 350;


void setup() {
  //interrupts for encoder
  PCICR |= (1 << PCIE1);    // This enables Pin Change Interrupt 1 that covers the Analog input pins or Port C.
  PCMSK1 |= (1 << PCINT10) | (1 << PCINT11);  // This enables the interrupt for pin 2 and 3 of Port C.
  //Serial.begin(9600);
  ss.begin(9600);
  timer.setInterval(100, sendSpeedPacket);
  encoderButt.attachClick(startStop);

  // Set Up EEPROM
  EEPROM.setMemPool(memBase, EEPROMSizeNano);

  //Load the stored speed value
  setSpeed = EEPROM.readInt(memAddress);

  display1.setBrightness(2);
  updateDisplay();
}

void loop() {
  // put your main code here, to run repeatedly:
  timer.run();
  encoderButt.tick();
  readEncoder();
  updateDisplay();
}

void sendSpeedPacket() {

  if(halt == true) { //send the stop packet
    ss.write((byte)0);
    ss.write((byte)255);
    ss.write((byte)241);
    ss.write((byte)2);
    ss.write((byte)0);
    ss.write((byte)0);
    ss.write((byte)221);
  }
  else { //send the current speed packet
    ss.write((byte)0);
    ss.write((byte)255);
    ss.write((byte)241);
    ss.write((byte)2);
    ss.write((byte)b1[curSpeed]);
    ss.write((byte)b2[curSpeed]);
    ss.write((byte)b3[curSpeed]);   
  }

  if (halt == true){
    curSpeed = 0;
  }

  if (curSpeed < setSpeed && halt == false) { //this ramps the sent speed up until it hits the set speed
    curSpeed++;
  }
  if (curSpeed > setSpeed && halt == false) { //this immediately reduces the sent speed to the set speed
    curSpeed = setSpeed;
  }

  //Serial.print(halt);
  //Serial.print(" ");
  //Serial.println(curSpeed);
  
}

void startStop () {
  halt = !halt;
  EEPROM.writeInt(memAddress, setSpeed); //save the speed every time the button is pushed... so that on next power up it is not zero
}

void readEncoder() {
  static int pos = 0;

  int newPos = encoder.getPosition();
      if (pos > newPos) {
        setSpeed = setSpeed - 1;
      }
      else if (pos < newPos) {
        setSpeed = setSpeed + 1;
      }
      pos = newPos; //--keep

      setSpeed = constrain(setSpeed, 0, speedCount);


}

ISR(PCINT1_vect) {
  encoder.tick(); // just call tick() to check the state.
}


void updateDisplay() {
  display1.showNumberDecEx(sfm[setSpeed], 0, true);  //Set time should always show on display 1
//  Serial.print("Minutes: ");
//  Serial.print(seconds / 60);
//  Serial.print("Seconds: ");
//  Serial.println(seconds & 60);
}

Update 11/2022 – here’s a list of some of the unique commands I saw. I didn’t test or try to capture the incline setting, but if we’re lucky maybe it’s in here:

These are the unique commands and responses I recorded just after powering up:
Source	Unique Message		Count	Comment
Panel	0 255 14 0 14			1	
Motor	128 0 14 0 5			1	
Panel	0 255 9 64 0 95 241		2	
Motor	128 0 9 0 107			2	
Panel	0 255 159 0 9			30	Common while idle and running
Motor	128 0 159 64 0 0 160	30	Probably reply to 0 255 159 09
Panel	0 255 143 64 0 0 187	81	Speed command? 143
Motor	128 0 143 0 238			81	


And here are the unique commands while it was running at a steady .5 mph:
Source	Message						Count	Comment
Panel	0 255 143 64 0 133 8		77	Speed command? 143
Motor	128 48 143 0 48				77	Same acknowledgement to speed command as 1 mph
Panel	0 255 111 64 0 20 14		76	Same while running at 1 mph
Motor	128 48 111 0 69				76	same response as running at 1 mph
Panel	0 255 159 0 9				23	Common while idle or running
Motor	128 48 159 64 0 37 56		11	
Motor	128 48 159 64 0 165 180		10	

bookmark_borderMini Lathe Power Feed Mount

Part 2 for the lathe power feed. I’ll make at least one more to go over the wiring and controls.

This mount puts the motor on the left side of the lathe and replaces the original banjo for the changegears. This is the easiest/cheapest way I could come up with to attach the motor to the leadscrew. Originally I intended to extend the leadscrew and drive it from the tail end, but that is a bit more challenging and I thought this would be easier for the home-shop. The motor will interfere with the cover, so you may need to cut a hole in it or leave it off. I put some thought into this so that it could be made with basic tools (no slots, welding, etc).

Bill of Materials

QuantityDescription
1Steel or aluminum plate 4″ x 5″ (or larger). 3/16″ or 1/4″ thick
4M5 x .8 Screw – 35mm long
1M5 x .8 Screw – 15mm long
4Spacer 5mm ID – .700″ long (see drawing)
(make from round stock)
1NEMA 23 Stepper (notes below)
1GT2 pulley – 60 tooth 12mm bore
1GT2 pulley – 20 tooth (bore to match stepper shaft)
1GT2 belt – 200 tooth, 6mm wide

Tools required

  • M5 x .8 tap
  • 4.2mm or #19 drill for M5
  • 8mm or O drill (.3160″)
  • 5mm or #7 or #8 drill
  • Tools for cutting the plate
    • Hacksaw and a drill is the basic requirement
    • Bandsaw, scroll saw, mill, CNC, LASER… these are optional. Use whatever you want
  • File or sandpaper
  • Lathe (for making spacers)

The only critical dimensions on this part are the four tapped M5 holes for mounting the motor. It is also important that the four spacers be the same length so the motor is square to the plate. Other than that, the outside contour of the part only needs to clear any obstructions. The circular part with the offset hole is used to adjust the belt tension, it rotates and presses against the leadscrew bushing/bearing – if that doesn’t make sense it should be more clear in the photo. The OD of the spacers is only important for the one that is closest to the leadscrew pulley, it may be necessary to relieve this for clearance.

I recommend printing the drawing out at 1:1 scale and gluing it to your plate, center punch the hole locations for drilling and use a saw and files to get the right outside shape.

Hopefully it is clear how this mounts up to the lathe in the photos. Take off all of the change gears including the key on the leadscrew and set them aside (or probably lose them forever, which is what I will probably do). The banjo also needs to be removed, but the nut and washer will be reused. Don’t tighten the set screws on the pulleys until you have the motor mounted, this way you can shift them around for alignment. Remember to put the belt on!

Regarding the motor – I chose a 1.9 nm stepper. I have not tested this thoroughly yet, but so far I have this running at 2 amps and it doesn’t seem to have any trouble moving the carriage while cutting in aluminum. I think a smaller motor will probably be sufficient – but I don’t know the limits yet.

That’s it! I’ll follow up with the details for the control in another post.

bookmark_borderPower Feed Prototype

Rainy day today, got the prototype firmware working. For whatever reason using a display and sending pulses to a stepper motor with the Uno was a real struggle. I tried several different libraries for the I2C LCD and stepper motor and wasn’t able to get a single update done quickly enough to fit between steps. This was easy to hear, sometimes you can see it, and depending on the pulse frequency it would even stall the stepper or cause it to reverse direction if it was changing speed at the same time. At this point I am just not updating the display in the “Jog” mode until you stop or change direction – the numbers are really meaningless anyways and if you’re cutting you should adjust it by looking at the tool not the screen. My 3D printer can do it without a problem, wonder how?